Max number of consectutive blanks in a range

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
In H1 enter1
In H2 enter:
=IF(G2="",H1+1,0) and copy down

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

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

Thanks again,

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

Don't want
No
Slow

Thanks again.

Gary''s Student said:
You are welcome! You can use an array formula instead of the helper column,
but it might get really slow.
Click to expand...
 
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.
 
Back
Top