Longest Streak of Zero and Non Zero numbers

[boxford]

Hello, first sorry for not using XL2BB, my company will not allow addons. I am looking for a way to count the longest string of days I have no sales and the longest string of days I do have sales. I got the array formula correct on the non sales number of days, but cannot get the longest to work.

Here is the formula I use for longest non-sales streak: =MAX(FREQUENCY(IF($A$2:$A$50=0,ROW($A$2:$A$50)),IF($A$2:$A$50<>0,ROW($A$2:$A$50))))

Any help would be appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=MAX(FREQUENCY(IF($A$2:$A$50>0,ROW($A$2:$A$50)),IF($A$2:$A$50=0,ROW($A$2:$A$50))))

 

[boxford]

That's it! Thank you so much. But now when I changed the absolute number below so that I can continue to add daily numbers, my streak of zero is now 979. Do I need to use a IFERROR in the formula somehow?

=MAX(FREQUENCY(IF($A$2:$A$1000=0,ROW($A$2:$A$1000)),IF($A$2:$A$1000<>0,ROW($A$2:$A$1000))))

 

[Fluff]

Try

=MAX(FREQUENCY(IF($A$2:$A$1000<>"",IF($A$2:$A$1000=0,ROW($A$2:$A$1000))),IF($A$2:$A$1000<>0,ROW($A$2:$A$1000))))

otherwise the blank cells are treated as 0

 

[boxford]

=MAX(FREQUENCY(IF($A$2:$A$1000<>"",IF($A$2:$A$1000=0,ROW($A$2:$A$1000))),IF($A$2:$A$1000<>0,ROW($A$2:$A$1000))))

Works great! Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.