CODEWITHSUNDEEP
excelexcel-formula
CiscoNewb
CiscoNewb

Reputation: 5

Excel - Count Empty Cells in a Column until a Non Empty Cell is Found Report then Continue down column till next non empty cell is found

Example of what I mean

A column that is 40 cells long.

The output will count how many empty spaces are till the first non empty value

The second output will show count how many empty spaces from the next first empty space till the next non empty space.

The third output will show count how many empty spaces from the previous non empty space to the next non empty space.

etc etc till it reaches the 40th cell.

(Quantity of outputs can vary not restricted to only 4 non empty cells in the column)

{=MATCH(FALSE,ISBLANK(B1:F1),0)-1}

This will give me the output for the number of blank spaces till the first non empty space. I've tried various different ways to try and get it to continue going through the cells and resuming form where it last reported, but couldnt get anything to work. Any suggestions?

Thanks!

Upvotes: 0

Views: 1113

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

In C2 enter:

=MATCH("*",B:B,0)-1

In C3 enter:

=MATCH("*",INDEX(B:B,SUM($C$1:C2)+ROWS($1:2)):INDEX(B:B,9999),0)-1

and copy downward:

enter image description here

Note(s):

  • the formulas treat the column as a series of blocks, each block being a set of sequential empty cells between two non-empty cells
  • from the position of the boundary cells, the formulas calculate the number of empties between them

Upvotes: 4

Related Questions