Click here to Skip to main content
15,568,809 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 26-Oct-21 17:28pm

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
 
Share this answer
 
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)
 
Share this answer
 
Comments
CHill60 27-Oct-21 7:15am    
But... XMATCH wasn't introduced until the 2019 update so wasn't around when the question was asked!

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