Need the Count of cells only if they occur consecutively

**bahanley** · 11-12-2015, 05:48 PM

I need to get the total of the cells only if the "U" occurs consecutively 6 or more times. For instance:

U U U U U U U U P P U U U U U U P U U U P P

In the line above, there are a total of 17 "U"s, but I only need the sum of the ones that occur consecutively 6 or more times, so in this case, the value would be 14.

I am using:
=SUMPRODUCT(--(FREQUENCY(IF(H19:AD19="U",COLUMN(H19:AD19)),IF(H19:AD19<>"U",COLUMN(H19:AD19)))>=6))

to identify that the 6+ consecutive times happens 2 times, but now need the count of those cells.

Thank you

**Tony Valko** · 11-12-2015, 05:55 PM

Try this array formula**:

=SUM(IF(FREQUENCY(IF(H19:AD19="U",COLUMN(H19:AD19)),IF(H19:AD19<>"U",COLUMN(H19:AD19)))>=6,FREQUENCY(IF(H19:AD19="U",COLUMN(H19:AD19)),IF(H19:AD19<>"U",COLUMN(H19:AD19)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**bahanley** · 11-12-2015, 06:04 PM

Great! That worked!

**Tony Valko** · 11-12-2015, 06:09 PM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Need the Count of cells only if they occur consecutively

LinkBack

Thread Tools

Rate This Thread

Display

Need the Count of cells only if they occur consecutively

Re: Need the Count of cells only if they occur consecutively

Re: Need the Count of cells only if they occur consecutively

Re: Need the Count of cells only if they occur consecutively

Thread Information

Users Browsing this Thread

Similar Threads

count all values larger than X AND occur more often than Y times consecutively

[SOLVED] Count how often multiple number values occur by using functions

[SOLVED] Count number of occurences of values over range of cells that occur at least Three times.

Count number a times a specific value appears consecutively.

How to use xml to count numbers that occur in roulette

Count when values occur in different arrays

formula to count how often something AND something occur.

Bookmarks

Bookmarks

Posting Permissions