Max number of consectutive blanks in a range

[Steve]

I have this formula in column
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$700)>0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$700),"")

In Column H, I like to count the maximum number of consecutive blanks in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18
to be returned.

Thanks,

Steve

 

[Gary''s Student]

In H1 enter1
In H2 enter:
=IF(G2="",H1+1,0) and copy down

In another cell the answer is:
=MAX(H:H)

 

[Steve]

Perfect.

I thought it'd be simple, but actually simplier than I thought.

Thanks again,

Steve

 

[Gary''s Student]

You are welcome! You can use an array formula instead of the helper column,
but it might get really slow.

 

[Steve]

This is fine.

Don't want
No
Slow

Thanks again.

You are welcome! You can use an array formula instead of the helper column,
but it might get really slow.

 

[T. Valko]

Try this array formula** :

=MAX(FREQUENCY(IF(H3:H700="",ROW(H3:H700)),IF(H3:H700<>"",ROW(H3:H700))))

** 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.

 

[Steve]

This works nicely also.

Thank you very much.

Steve

 

[T. Valko]

You're welcome. Thanks for the feedback!