Consecutive Counts

[Jarbugs]

Hi there,

I have a sheet that lists customers in column A and then each customers Distribution & Sales over a period of time. The spreadsheet then flags if the customer has sold out in any month. I now require a count of maximum consecutive sellouts for each customer. For example:

Customer A S/O S/O nil nil S/O
Customer B S/O S/O S/O nil nil
Customer C nil S/O S/O S/O S/O

In the above example Customer A would have 2, B 3 and C 4 sellouts. I'm looking at about 10K customers I require for each product.

Hope someone can help!

[Ron Coderre]

With your sample data in A1:F3

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER (instead
of just ENTER) returns the max consecutive occurrences of "s/o" for
Customer A

I1: =MAX(FREQUENCY(IF(B1:H1="s/o",COLUMN(B1:H1)),
IF(B1:H1<>"s/o",COLUMN(B1:H1))))

Copy I1 and paste into I2 and down as far as you need.

Is that something you can work with?

[ikuogs]

Fantastic. May I understand the logic behind the last part of the formula?
IF(B1:H1<>"s/o",COLUMN(B1:H1))

[Ron Coderre]

The FREQUENCY function rounds test values UP to the next "bin" value, ignoring text and TRUE/FALSE values.

Example:

Using Customer A of your posted data in A1:F1

Customer_A    S/O      S/O      nil     nil     S/O

Test          ColNum				
=S/O          2        3        FALSE   FALSE   6
<>S/O         FALSE    FALSE    4       5       FALSE

=FREQUENCY(IF(B1:H1="s/o",COLUMN(B1:H1)),IF(B1:H1<>"s/o",COLUMN(B1:H1)))
=FREQUENCY({2,3,FALSE,FALSE,6},{FALSE,FALSE,4,5,FALSE})
=FREQUENCY({2,3,6},{4,5,Over_5})
={2;0;1}

2 and 3 count in the 4 bin
0 for the 5 bin
6 counts in the Over_5 bin

I hope that helps