R Radon Apr 12, 2009 #1 I need to count the number of blanks in a range. I'm trying countif(H1:H20,"<>""") but it's not working. Any ideas?
I need to count the number of blanks in a range. I'm trying countif(H1:H20,"<>""") but it's not working. Any ideas?
D Don Guillett Apr 12, 2009 #2 =COUNTIF(H1:H20,"<>"&"") Or to account for the "dreaded SPACE BAR" =SUMPRODUCT((LEN(TRIM(H1:H20))>0)*1)
=COUNTIF(H1:H20,"<>"&"") Or to account for the "dreaded SPACE BAR" =SUMPRODUCT((LEN(TRIM(H1:H20))>0)*1)
T T. Valko Apr 12, 2009 #3 This will count both empty cells and cells that contain formula blanks (""): =COUNTBLANK(H1:H20)
R Ragdyer Apr 12, 2009 #4 Looking at your formula example, I'm assuming you want to *not* count zero length strings ("") that might be returned by formulas already existing in the referenced range. This will *not* count < "" >, or < spaces > produced by the space bar: =SUMPRODUCT(--(ISBLANK(H1:H20)))
Looking at your formula example, I'm assuming you want to *not* count zero length strings ("") that might be returned by formulas already existing in the referenced range. This will *not* count < "" >, or < spaces > produced by the space bar: =SUMPRODUCT(--(ISBLANK(H1:H20)))
