Click here to Skip to main content
14,983,791 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 18-Oct-16 11:03am

1 solution

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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900