Counting Non-Blank Cells

Home/Functions/ Counting Non-Blank Cells

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.

2017-03-08T03:53:59+00:00 Functions|