Counting non-blank cells
I have a range of IF functions that return “” in certain circumstances. I want to count the number of cells without “”, but COUNTIF won’t do it. Is there a way to count the non-blank cells in the range?
By Neale Blackwood
The SUMPRODUCT function can count non-blanks. Assuming the range is C1:C20 the following formula will count the non-blanks, or those cells without “”.
=SUMPRODUCT((C1:C20<>””)*1)
The <> symbols in the formula mean not equal to.
You could also use this formula:
=COUNTA(C1:C20)-COUNTIF(C1:C20,””)
COUNTIF will count blank cells but not non-blank cells.