Excel Count Concurrent Values In A Row

ilfocorp used Ask the Experts™
Hello programmers.  I have a 2010 spreadsheet that has a column with currency values from $0 and up.  I would like to count the maximum number of times the value $0 occured concurrently before a value greater than $0 occurred.  Please see attached simple one column spreadsheet.  For example, row 2 thru 4 equals 3 and row 10 thru 11 equals 2, so I want in a separate cell, "Max Number of times $0 occurred concurrently", the number 3.  If it's possible.  Thank you.
Row 1: $80.00
Row 2: $0.00
Row 3: $0.00
Row 4: $0.00
Row 5: $87.00
Row 6: $122.00
Row 7: $108.00
Row 8: $62.00
Row 9: $20.00
Row 10: $0.00
Row 11: $0.00
Row 12: $22.00
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you can afford a helper column then it will be very easy.

Enter this formula in B2 (or any other cell in row 2) and copy it down.


Then you can pick up the maximum of that column.


This will work.  Thank you.
If you are not happy with the solution why did you not say so? You should always give a chance to improve it before assigning a B grade.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial