Counting Consecutive Cells With Value 


Counting Consecutive Cells With Value  Excel 
View Answers 
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.
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.
Similar Excel Video Tutorials
Avoid Data Validations 1 Rule Limit
 See how to work around Data Validations one rule limit by creating a second shrinking list that looses values as they are used in the Data Validation ...
Avoid Data Validation 1 Rule Limit
 See how to work around Data Validations one rule limit by creating a second shrinking list that looses values as they are used in the Data Validation ...
ROW & COLUMN functions
 See how to create array formulas with the ROW & COLUMN functions in order to create consecutive Integers.
Also see how to use the INDIR ...
Also see how to use the INDIR ...
Count Unique Items With More Than One Criteria
 Amazing use of the FREQUENCY and MATCH functions! Array formula for counting unique items in a column with criteria in a second column. See 1 formula ...
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
 Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
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
 This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Delete Empty Columns
 This macro will delete columns which are completely empty. This means that if there is no data within the entire column
 This macro will delete columns which are completely empty. This means that if there is no data within the entire column
Count The Number of Words in a Cell or Range of Cells in Excel  With UserSpecified Delimiter / Separator  UDF
 UDF to count the number of words in a cell or range with a userspecified delimiter. This means that you can tell the f
 UDF to count the number of words in a cell or range with a userspecified delimiter. This means that you can tell the f
Name Worksheets Based on Cell Contents
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
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!
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.
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
http://www.excelforum.com/excelgene...than0a.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
Using the first formula on this thread
http://www.excelforum.com/excelgene...than0a.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
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
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 nonzero values across 33 columns without resorting to a horrid nestedIF 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 am struggling to find a way to count a number of consecutive nonzero values across 33 columns without resorting to a horrid nestedIF 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
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
=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?
=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?
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
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
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
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
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
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 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".
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:
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!
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!
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
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:
5
7
0
5
0
0
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 nonblank cell is any value except zero then the result
shound be "0".
5
7
0
5
0
0
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 nonblank 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.
Thanks!!
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
Any ideas?
Thank you,
BigDawg15
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.
Example..
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....
I am trying to find consecutive numbers within a cloumn by using the logigal formula =if.
Example..
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 0103041112. How do I go about creating a formula on B1 to identify the consecutive numbers in that format? There are two consecutive in A1 (0304, and 1112).
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!
xoxo
For an example. On A1, I have 0103041112. How do I go about creating a formula on B1 to identify the consecutive numbers in that format? There are two consecutive in A1 (0304, and 1112).
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!
xoxo
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
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
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?
Thanks
Tim
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?
Thanks
Tim
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 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 nonconsecutive "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 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 nonconsecutive "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!
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; http://briefcase.yahoo.com/unclemonty@sbcglobal.net 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,
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,