15,568,809 members
See more:
Hello everyone,

Hope you are doing great..!!

I have this excel sheet and I need to count blank cells from a range till a non blank cell is reached.

A | B | C | D | E | F | G | H | Count
x | x | - | - | - | x | - | - | 2
x | x | - | - | - | - | - | x | 0

In first row since the last column is blank, so next values are considered. Output is 2 cause there are two blanks before any non-blank cell.

In second row, since last column has value, so it will straight away print 0.

What I have tried:

=IF(cell="",something_to_count_blanks,0)

how to count those blank cells?
Posted
Updated 26-Oct-21 17:28pm

## Solution 1

Try using this formula(For me this formula was in cell I2)
=COLUMNS(A2:H2)-COUNT(A2:H2)-LOOKUP(2,1/(A2:H2<>""),COLUMN(A2:H2))
C#
```A	B	C	D	E	F	G	H	Result
x								7
x							6
x						5
x					4
x				3
x			2
x		1
x	0```

## Solution 2

You can try this, we have the same problem, was able to come up with this. (Thanks Google) Works great with pivot tables. Formula is in I1.

=COLUMNS(A1:H1)-XMATCH("*?",VALUETOTEXT(A1:H1),2,-1)