Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Counting Consecutive Cells With Value

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I use Excel 2007 and I'm trying to find code or a formula that would count the number of consecutive cells in a row from left to right until it there is 0, Below is an example of what I'm trying to do:

A1 B1 C1 D1 E1 G1 H1 I1 Result 32 0 0 123 123 0 1232 123 2 32 0 123 123 123 123 0 7 1 32 1231 123 123 123 123 8 7 0

Column J has the results.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight

Similar Topics

I want to find a way to fix this:
I have a series of numbers and I want to find a formula for the number of consecutive values of at least a certain number.

1 6 1 4 2 3 2 5 7 3 0 5 5 1 4 5 4 1 1
Gives us:
Max consecutive for at least 1 gives 10
Max consecutive for at least 2 gives 7
Max consecutive for at least 3 gives 3
Max consecutive for at least 4 gives 3
Max consecutive for at least 5 gives 2
Max consecutive for at least 6 gives 1
Max consecutive for at least 7 gives 1

Hope you understands my question and can help!

Good Afternoon:

I created an excel formula to count the number of consecutive zeros in a column. I am interested in one consecutive zero, two consecutive zeros, three consecutive zeros, etc...

I used the following formula:

=SUM(IF(FREQUENCY(IF(P$2:P$279=0,ROW(P$2:P$279)),IF(P$2:P$279<>0,ROW(P$2:P$279)))=W2,FREQUENCY(IF(P$ 2:P$279=0,ROW(P$2:P$279)),IF(P$2:P$279<>0,ROW(P$2:P$279)))))

The formula works 98% of the time. It generally produces the correct number of consecutive zeros, however, occassionally it will indicate there are 15 sets of zeros = 15. Can anyone provide any guidance as to what should be done with this formula?

Thank you for your time.

My goal is to look at a column of numbers and count the number of consecutive cells that are positive (or negative), while ignoring blank cells that may be in between them.

Using the first formula on this thread

I was able to adapt it to my own data, but whenever I had blank cells in my data the results were not reliable.

the formula i am using now is
=IF(COUNT(1/((A1:A27<0)*(A2:A28<0))),MAX(FREQUENCY(IF((A1:A27<0)*(A2:A28<0),ROW(A2:A28)),IF((A1:A27<0)*(A2:A28>= 0),ROW(A1:A27))))+1,0)
for a bunch of data in column A.

With limited knowledge of excel I'm not exactly sure how this formula works, so I'm not sure how to tell it to ignore all blank cells.

I attached an example that shows how it fails with the blank cells

Hopefully someone can help me out!
Thanks a lot

Similar question to one asked recently:

I would also like to be able to find the maximum consecutive occurrences of "." for each person when results are combined with previous entries. For example, Tom's first result finishes with 4 consecutive "." When combined with his second result which begins with one "." before an "x" the formula result should be 5. (see below)
One person may have 100 different entries. I just need the single maximum consecutive occurrences of "." for each individual when spread across different rows.

John .xxxx
Mary ..x...
Lisa ...xx
Tom .x....
John x.....
Tom .x.xx.
Lisa x.x..x
Mary ......


Hi I wonder if anyone can help me with a simple query.

I have a list of numbers and I want to count only the consecutive cells which contain a number, and then stop counting when it reaches the first blank cell.




In the above I want a count of the numbers 1,2,8 and 6, so a total count of 4.

Wondered if anyone knew of a formula for this?



I am struggling to find a way to count a number of consecutive non-zero values across 33 columns without resorting to a horrid nested-IF formula.

I'm using Excel 2003 and each row I have is an account (there are 5572 accounts). I have been tasked to check if a customer has made 6 consecutive payments (held on each row in columns K to AQ.

If a customer has made less than 6 consecutive payments then I need to multiply the total collected by 40%. If the customer has made more than 6 consecutive payments then I need to multiply the first six payments by 40% and then each subsequent payment (consecutive or otherwise) by 20% and total the figure.

Can anyone help?

I'm trying to count the number of consecutive cells that are greater then $35, does anybody know how I can do this?

Thanks in advance, Andy

Hello, i've been using the below formula to find the most consecutive "W" (Wins) without any "L" (Loses) in 2 rows, there are between the "W" blanks and \ and numbers


The problem is at the start or end of the count eg
The above returns 5 the result i am looking for is 3 as column B and C are the only ones without an L, there are alot more columns so obviously if there are larger sets of consecutive W then the result would be bigger, any ideas?

I've been stumped for days on finding a formula that will sum the values of cells in a column that are in runs of 5 or more consecutive cells where the cells in the run have a value >=3.

I have found a formula that gives me the count of runs, but I also need a formula that will give me the total, adding only the cells within those consecutive runs of >=5 cells with values >=3.

The following formula gives me a count of runs:
confirmed with ctrl+shift+enter.

For example:
Column A

Using the formula mentioned above would result in a count of 2 runs (5,3.4.6,3,4 and 3,3,3,4,3). I need a formula that would add ONLY the values in these runs and give me a result of 41. Is there a way to create a single formula that would do this as opposed to using several helper columns to calculate the desired result? I apologize for the lengthy explanation.

I greatly appreciate any assistance!


Hi, i've trawled through the posts on the board and can't find an answer to the problem i'm trying to address.

If i have a row of numeric data, what formula can i use to count the number of consecutive zeroes in the row? It sounds simple, hopefully it is

Thanks for your help,



Is there a way of counting the number of times a string of consecutive cells appear in a range?


How many times are there 5 consecutive cells with a value of below a set number referenced in B2 (say number 15), in the range A1 to A100.

Many thanks,


Hi everyone, I have a slight problem I need hekp with.

In column E I have a field that contains either "Yes", "No". Now what I am trying to do is figure out the maximum and minimum of consecutive "Yes" and consecutive "No". I am also looking for the average consecutive "Yes" and the average consecutive "No".

Hi all,
I have a column of data containing only 1's and 0's and I would like to use a formula to count the number of mostly consecutive ones. Thanks to previous posts I have already counted the largest number of consecutive ones. But I also need to count the number of ones with a single 0 between them. Here is an example:


largest number of consecutive ones: 3
largest number of consecutive ones with a single 0 between: 6

Please let me know if this can be done, and if so, how to do it.

I want to count numbers excluding zero in consecutive line cells.
example: H3=2, I3=0, J3=5, K3=0, L3=2) In cell M3 the number should read 3

what is the formula for this?

Thank you, Ironsides

I have a column of numbers like this:

Empty Cell
Empty Cell

And I want to count the number of consecutive zeros at the end of the
list ignoring any blank cells. In the above example the result would
be "2" - two consecutive zeros and the two blank cells are ignored.
Thanks for any help you can provide.

if the last non-blank cell is any value except zero then the result
shound be "0".

Hi All,

I'd like to count how many times a numerical value repeats in a range as a consecutive duplicate - I didn't see any related posts...

For example, the value is in A2 and the range is D2:Z2 and I'd like to place the formula in B2 to return the # of consecutive duplicates.

With comma's separating cells, let's say value is 3 and range is: 4,6,8,3,3,5,3,3,8,9,2 etc.

In this short excerpt the result would be 2.


First time posting, and have learned much from the information here. I have searched for a formula or macro to count a letter "U" if it is found in two or more consecutive cells, i.e. U S U U should show 2 in the total box (two consecutive "U"s). I can't seem to find anyway to do it without helper rows.

Any ideas?

Thank you,


I need help in figuring out a formula that will give me the correct results.

I am trying to find consecutive numbers within a cloumn by using the logigal formula =if.


I have a column that shows random numbers but some are consecutive. I am trying to group those that are in consecutive order.

I have the following number in the column:
A1 9
A2 7
A3 8
A4 9
A5 3
I need to group 7,8,9 in one group.

In the next column I am entering =if(a2=a1+1,True,False)

This works except that the last one in the group (A4) will come up as false because A5 is no longer consecutive. It always leaves the last one as false and therefore I cannot group them correctly. Is there a formula that would group these together?

Please Help....

My sheet is used to enter lab test results in a row. I am entering the test date and result (e.g. cells H4:K4 - "3/13 N"; "3/20 P"; "3/27 N"; "4/3 N"...). My goal is to have a Status cell (B4) return a "Yes" when four consecutive "N" results (negative) appear (informing me that the testing for that variable can stop). The Status cell would need to return "No" until such time as four consecutive "N" results are entered. Macro is an option, formula would be preferred. Excel 2003. Any ideas? Thanks for your time... Bruce

I know how to identify a number, but not consecutive number.

For an example. On A1, I have 01-03-04-11-12. How do I go about creating a formula on B1 to identify the consecutive numbers in that format? There are two consecutive in A1 (03-04, and 11-12).

I would like to have B1 let me know there is consecutive in A1 by placing a symbol √ on B1. Is it possible?

If not the symbol than a 1 would do, does not matter how many consecutive are in A1, by placing number 1 lets me know that A1 have at least 1 consecutive.

Thank you very much!

Hi Guys,

I need some help here.

I need a code that will go through the entire worksheet, and search for consecutive blank cells, and deleting certain rows based on the number of blank cells:

E.g. Column A is the column that I want to look for blank consecutive blank cells.

if no. of consecutive blank cells =2, delete rows with blank cells, and rows before and after the consecutive blanks.

Attached a screenshot with an example and the rows i want to delete.

ANy help is appreciated.


Does anyone know how to count consecutive matches?

For instance:

Column A has ticker symbol.

Column B should be the rating duration (how many consecutive months having the same rating as the current rating)

Column C has the current rating (Positive, Neutral or Negative).

Column D is the rating 1 months ago, Column E is the rating 2 months ago etc up to 24 months.

Is there a formula that will evaluate C2 relative to D2:Z2 and return the consecutive matched going from left to right starting at D2?



Can someone point me in the right direction for a formula to count the most consecutive non black cells in a row please.

I have days in columns c4 to FD4 ( days of the week from 4/01 to 11/06) and need to see how many consecutive days in the row below have an entry.

Hope that makes sense


I did a search of these forums, and after looking through 10 pages of results, I was unable to find an answer, so I'm hoping someone can either help me, or point me to the solution...

I have about 30,000 rows of data, and the first column is numbered NEARLY consecutively.

1 data data data
2 data data data
3 data data data
4 data data data
7 data data data
8 data data data
9 data data data

(note "5" and "6" are missing, but there may be occurrences of 1, or 3 or more, missing consecutive numbers.)

Is there any way (via macro or otherwise) to identify the non-consecutive "gaps" then insert a row, then fill the numbered column with the correct "consecutive" number?

So, the end result would be a perfectly consecutive numbered column, (but with blank data in the newly created rows), like so...

1 data data data
2 data data data
3 data data data
4 data data data
7 data data data
8 data data data
9 data data data

Any help would be greatly appreciated!

Hello, I have a row in which some cells have numbers and some cells are blank. To show an example of what this spreadsheet looks like have posted a jpg of it here; It is example 1. This is just an example. My rows contain many more cells.
What I am trying to calculate is the average # of consecutive blank cells in the row. In my example the answer would be 2.5 The total # of blank cells is 10 and there are a total of 4 areas of consecutive blank cells. 10/4=2.5 My question is what formula or combination of formula would I use for this calculation? I have been able to use the count formula (example #2 and #3 in the weblink) but, I can't figure out how to have excel tell me there are 4 areas of consecutive blank cells in this example.

thank you for for time and consideration,