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

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 Tutorials

Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
TRIM - Remove Spaces From the Sides of Text and Extra Ones in the Middle in Excel
This allows you to make sure that there are no hidden spaces at the start or end of the text in a cell and also tha ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Determine if Cells Contain a Specific Value in Excel
Find if a cell or range of cells contains a specific value in Excel. This method can be used on individual cells wh ...
Average Non-Contiguous Cells in Excel
How to average non-contiguous cells (cells that aren't next to each other) if those cells contain numbers and are ...
Link to Cells on Other Worksheets in Excel
It is very easy to link one cell to another cell in Microsoft Excel. The steps needed are listed below as well as p ...

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

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


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, 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,


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.



I have several thousands of numbers in a column. I would like to add every 100 cells and bring the result into column B. Can someone help me with this?

For example if I have numbers in 1000 cells from A1 to A1000. Now, I would like to add each consecutive 100 numbers (like A1:A100, A101:A200, A201:A300 and so on) and put the results in column B.

Please help me with this. Thanks

If I have a row and in each column of that row there is a letter value. If there is let's say an "X" in 5 consecutive columns of that row and I only want to count 3 of them, then 10 columns of "W's" then another 5 columns of "X's" and once again only want to count 3 of them as well, what is the formula?

Hello everyone,

I have been sent an excel sheet from a teacher of mine, but he is not available at the moment so I can not ask him. I have spent hours trying to figure it out myself, but I just can`t grasp it on my own.

Basically, the sheet is a montecarlo simulation. 5000 simulations of 10 trades in the stock market, where 0 equals a loss and 1 is a winner.

The purpose of the sheet is to find the probability of getting 1,2,3,4,5,6,7,8,9 or 10 consecutive winners or losers.

For that purpose, he has used the FREQUENCY function, but I just do not understand the formula and the way it is done.

I understand the sheet and all, but I am simply stuck on the formula.

I copied and pasted the relevant part of the sheet into a new sheet that I attached.

I would be very thankful if anyone could walk me gently through each step of the formula for consecutive wins and consecutive losses. I seem to have some problems understanding the use of the COLUMN function in the formula as well.

Thanks in advance!

Best regards,


I need to be able to tell when somebody has worked 5 consecutive days. My workbook records a 1 in the cell that correlates to each day worked. I need to be able to display a warning message when somebody works their 5th consecutive day. i need to stop counting when an empty cell is encountered and then continue counting at the next cell with a 1.

Thanks in advance for your help.

I need to count consecutive blank cells in between nonblank cells. For example:

Column A:

I need the cell next to "A" to return the number 2 because there are two blank cells in between A and "H", the next nonblank cell (counting upwards).

So the cell next to "V" must return the number 1 because there is only one blank cell in between V and the next nonblank cell, going up.

Please help. I tried a couple CountIf and IFBLANK formulas with no success.

Thanks a lot,


for example if you have values of

1 0 1 1 1 0 0 0 1 1 1 0 0 0 1 1 0 0 0 1 1 1
how to count number of occurances where value of 1 is 3 consecutive times? Obviously answer is 3 in this instance but is there an excel formula to count/sum this?

Thanks in advance

I want to count the number of consecutive letters in a string that match from an array of letters.

If the array of letters is a,e,i,o,u. And I have a string "aeon". It would count 3, because there are 3 letters "aeo" in a row that match from the array. (note: all of the strings will be 4 characters long, if that helps any)

This seems REALLY complicated in my head, and I haven't been able to process it through.. I was hoping somebody here had an idea as to how I could do this.

I already have a formula from this board that I used to count letters in a string, but I don't know how to do it for consecutive letters..



This counts the number of times A occurs.

As always, big thank you to any help.

I am looking to count the number of consecutive values >=2 over 3 different rows.....I was helped greatly the other day on how to do this on a row, but since then I have been looking to get this to work over 3 rows.


Originally Posted by daddylonglegs

I assume you want the maximum number of consecutive values >= 2. For that try this formula


confirmed with CTRL+SHIFT+ENTER

There will 3 different rows with 18 columns (golf database) so 1 row will be populated at a time. So ideally I would like to get daddylonglegs equation to work over A2:R2 & A4:R4 & A6:R6....this way it will calculate the for day one, then also take into account day 2 and then also day 3.

I have tried :-
=MAX(FREQUENCY(IF((A2:R2,A4:R4,A6:R6)>=2,COLUMN(A2:R2,A4:R4,A6:R6)),IF((A2:R2,A4:R4,A6:R6)<2,COLUMN( A2:R2,A4:R4,A6:R6))))

Error I believe is to do with the Cloum part, but I do not fully understand it all.

I have a column of numbers (I'll make up an example here).

Column G

I'm trying to devise two formulas:

1. Returns the maximum string of consecutive numbers smaller than -2%. In my example above: 3 (ie the string of -5%,-10%,-11%)

2. Returns the number of occurrences where at least 2 consecutive numbers smaller than -2% occur. In my example above: 3 (ie "-4% and -3%", "-5%, -10%, -11%", and "-4%, -5%").

For the first, I tried using combinations of the frequency and max formulas. For the second I tried using the sum and frequency formulas. Both instances aren't matching up with my "counting by hand". I've tried searching the internet, but I'm not having any luck...



I am new to Excel. Please help me with the below problem:
I want to write a script which will count the number of consecutive blank cells in a column. for eg: Assume A1 contains "one" and A5 contains "five". the cells in between A1 and A5 are empty. i want to get a count of the number of blank cells in between A1 and A5, using script. Is there any way i could achieve this?


I'd like to build a formula to count consecutive non-zero cells in a row, and to have the count stop when a zero is reached

1 0 0 0 0 1 2 1 1 answer 4
1 0 0 0 4 2 3 4 1 answer 5
1 1 1 1 1 1 1 0 1 answer 1

Any thoughts?

Hi All,

Can anyone help me with a Formula to COUNT how many numbers are consecutive
within a Row that spans 10 Columns?

65 67 68 69 75 79 80 84 85 90

The answer to the above example should be a Count of 7.

Much appreciated.


Message posted via

Hi. I have been working on this problem for several days and I need help from people who are smarter than me.

I have a spreadsheet where up to 20 samples are entered manually into a table (D3:D22). I want to find the samples that are consecutively increasing that meet or exceed a consecutive data up value in A3. If the data meets this consecutive up row count value I want to display those data values and ignore all the others. I have attached a copy of the spreadsheet to help better illustrate what I am trying to do. There are two scenarios described within this spreadsheet attachment.


Hi everyone,

I'm new here (and I did already one mistake writing in the thread...)

I have a question connected to a previous thread:

Is there a way to highlight the group of consecutive cells according to a predefinite criteria (not "just" count)?

Thank you in advance !


Can you give me some pointers please with a problem I am having trouble to solve.

I have a list of numbers with a references which are sorted lowest to highest for each reference:

For Example

B1 1 B1 2 B2 3 B2 1 B2 4 B3 12 B3 13 B3 15

What I want to do for each B reference is where each of the number cells is a consecutive number, convert this to a range with highest to lowest with a comma prefixed before the range

Where the number isnt consecutive, i just want to prefix the comma with the number.

For example B3 would be 12-13,15

Can anyone help please?

Hi everyone.
Couldn't find an answer to this one so here goes.

This array works great to count consecutive occurrences of a negative number, but I need it to skip text values of "NA" or blank cells - essentially ignoring everything except negative and positive values.


Any suggestions??

Thanks in advance.

Hi everyone,

The vba formula described down below should count consecutive values but it doesn't seem to work properly.
It should count i.e. 150,151 = 1 or 150,152 = 1

My question is; does anyone knows a different vba formula that will do this (if possible a normal formula instead of a array formula).

Thank you for any help offered.


[A1].FormulaArray = "=SUM(0+(R10C:R20C-TRANSPOSE(R10C:R20C)=1))"


I have an excel spreadsheet to record employee holiday and sickness figures.

It is set on as a grid e.g. column A stores all the dates and then employee names are used as column header.

One of the triggers I need to use is where, an employee has been absent 28 consecutive days. When an employee is absent I simply enter 'ABSENT' against there name.

Countif will count the number of time absent appears in the column however I need it to only recognise it if it is only 28 days in a row.

Any help would be much appreciated!



I'm trying to sum cells that are not consecutive and I'm getting an error that says "You've entered too many arguments for this function"

I went to help, but I still could not make heads or tails as to why. Are there a limited number of non-consecutive cells that can be entered in a formula?

I hope this is clear.....

Thanks for any help


E20 with the series of numbers in tenth, and with five numbers on one cell. Between 01-37. How do I write a formula on column R to identify any repeated numbers from E20 with E21? In the chart it shows number 10 is repeated from E21, so I place a check mark on the same row as E20 on column R.

On column S how do I write a formula to identify numbers within E20 to see if there are any consecutive numbers? On E20 there is none, on E21 there is one 10-11, and column S will let me know if there is a consecutive by placing a check mark.

Appreciate any help with this, right now I am manually doing this by hand.