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

I will use the formuula to fill ddown for a table that may be as large as 400,000 row?s. (so the less complicated the better on calculating time).

Explanation / Answer

Hi.

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

**Array formulas are not entereed in the same way as 'standard' formmulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If yoou've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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