Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

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







Hi!
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.

Example:
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.




Hi Guys,
I have a spreadsheet that has the columns conditionally formatted depending on cell value. I was wondering if it's possible to count the maximum consecutive cells across rows. I've attached a small sample. The result I want is maximum number of consecutive blue fonts in the whole spreadsheet which in this case is 4. Many thanks in advance.
Mop.

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
http://www.excelforum.com/excel-gene...-than-0-a.html

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

Thanks


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.

i.e

1
2
8
6

1
6

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?

Thanks
Rich


Hi,

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?


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


Greetings!
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:
=SUM(--(FREQUENCY(IF(A2:A721>=3,ROW(A2:A721)),IF(A2:A721=5))
confirmed with ctrl+shift+enter.

For example:
Column A
0
0.5
0
5
3
4
6
3
4
2
0
3
3
3
4
3
1

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!

-Juan


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

=MAX(FREQUENCY(IF($Y5:$DP5="W",COLUMN($Y5:$DP5)),IF($Y5:$DP5"",IF($Y5:$DP5"W",IF($Y5:$DP5"\",IF($Y5:$DP5>0,COLUMN($Y5:$DP5)))))))

The problem is at the start or end of the count eg
A B C D
L WWW
W W L
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?




My goal is to look at a row of numbers and count the number of consecutive cells that greater than 1. while ignoring 1 cells this cell is lower than 1 that may be in between consecutive cells. This can be a inaccurancy.

Now i got this:
=MAX(FREQUENCY(IF(C4:C11591>1;ROW(C4:C11591));IF(C4:C11591<1;ROW(C4:C11591)))).

This function count only the number of consecutive cells but it don't ignore if one cell is lower than 1 in a row.

In the excel file you can see more details about the problem.

Can somebody help me?



Hello all!. I need to count consecutive numbers, but with a condition.

i suppose this would be done in one or two steps, the example spreadsheet is attached.

I have two columns.
one for temperature M26:M43826, the other is relative humidity N26:N43826.

Part1

What i want to do is,
for every value that is >=5 and <40 for M26:M43826 AND >=80 in column N26:N43826
should be designated a value 1 in cells Q26:Q43826

if either of those two criteria are not met, then a value of 0 should be assigned in Q26:Q43826

Part2

ask excel to count the number of maximum consecutive cells in Q26:Q43826 and insert into P:15.

I'll use a small section of the dataset to use as an example
lets say in the column Q27-Q34 we get the following values:. In P:15, the value should be 3
0
1
1
1
0
0
1
0

thanks for the help
-Bismo

Hi,

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

Example:

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,

Pedro


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,

Nick




I want to count consecutive values and save each value to a report. So I have hundreds of these, 1 col wide, 48 rows. These contain the word blank and this is what I want to count. I want to count consecutive blanks. So the example below I would have a report that shows me....

Consecutive Blanks with corresponding count value
1 consecutive blanks = 2
2 consecutive blanks = 2
3 consecutive blanks = 2
4 consecutive blanks = 2
5 consecutive blanks = 0
6 consecutive blanks = 0
7 consecutive blanks = 0
8 consecutive blanks = 0


You get the idea....don't have to show 0's. I would want to be able to select multiple sheets within 1 book.


Blank
Blank
Server
Server
Server
Server
Server
Server
Blank
Server
Server
Server
Server
Server
Server
Server
Server
Server
Blank
Server
Server
Server
Blank
Blank
Blank
Blank
Server
Server
Blank
Blank
Blank
Server
Server
Server
Blank
Blank
Blank
Server
Server
Server
Blank
Blank
Server
Server
Blank
Blank
Blank
Blank

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:

1
0
1
1
0
1
1
1
0
0
1
1

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.
Thanks!

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.

Thanks!!


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,

BigDawg15

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.

Thanks


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


Hi,

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

etc...
(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
5
6
7 data data data
8 data data data
9 data data data

Any help would be greatly appreciated!


I need to be able to tell when someone has worked 5 consecutive days. My workbook places a 1 in the cell that correllates to each day worked, but how can I count the instances where somebody works 5 consecutive days and set up a warning that they can not work a 6th day? I need to be able to stop counting when I get to an empty cell then start counting again at the next cell that has a 1 in it.

Thanks in advance for you help


Is there a function that will allow me to count the number of non consecutive periods within a series of dates.

For example the following dates, contained within say A1:A6, should produce the result 3,

22/06/07 23/06/07 25/06/07 28/06/07 29/06/07 30/06/07

there being three non consecutive periods
22/06/07 to 23/06/07
25/06/07
and 28/06/07 to 30/06/07

thanks for looking

Nick


I need to create a formula that determines the highest number of consecutive cells over 10.

I have sales data starting in cell b1 all the way down to b1044 each cell contains the number of sales for one day. I want a formula that tells me what the highest number of consecutive days selling 10 or more units. For instance:

b1 = 7
b2 = 12
b3 = 15
b4 = 3
b5 = 22
b6 = 27
b7 = 11
b8 = 12
b9 = 2
b10 = 11
b11 = 10
b12 = 13
b13 = 7


I would want the formula to return 4....the maximum number of days in a row this person sold 10 or more units. (b5 - b8 in this example).


Hello,

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