count blank cells down to next value I need to count a total of blank cells ( pl

Question

count blank cells down to next value

I need to count a total of blank cells ( plus the starting cell with a number) down to the next number (not including that one).

If there are no blank cells, but the next row has a number then the result should be 1.

Formula would be where the "total" number is.

Blank cells between session counts could be as high as 5000.

I will use the formula to fill down for a table that may be as large as 400,000 rows. (so the less complicated the better on calculating time).

thanks in advance:-)!!,

Explanation / Answer

Shouldn't the result for cell F9 be 8, not 9?

You could try this array formula** in F3:

=IF(B3="","",MATCH(TRUE,B4:B5003<>"",0))

and copy down as required.

I have deliberately chosen a range of 5000 cells over which to calculate, since this was the value you gave as an upper limit on the number of blank cells between two values. Obviously if this needs to be increased then so be it, though the larger this range, the more calculation Excel has to perform.

Regards

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.