## How to use the COUNTIF function to count not blank cells

The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can also highlight empty cells using Conditional formatting.

I will discuss and demonstrate the limitations of using the COUNTIF function and other equivalent functions that you also can use.

**What's on this page**

## 1. Count not blank cells - COUNTIF function

Column B above has a few blank cells, they are in fact completely empty.

Formula in cell D3:

The first argument in the COUNTIF function is the cell range where you want to count matching cells to a specific value, the second argument is the value you want to count.

COUNTIF(*range*, *criteria*)

In this case, it is "<>" meaning not equal to and then nothing, so the COUNTIF function counts the number of cells that are not equal to nothing. In other words, cells that are not empty.

## 2. Count not blank cells - COUNTA function

The COUNTA function is even easier to use, you don't need to enter more than the cell range in one argument. The COUNTA function is designed to count non-empty cells.

COUNTA(value1, [value2], ...)

Formula in cell D4:

## 3. COUNTIF and COUNTA function return unexpected results

There are, however, situations where the COUNTIF and COUNTA function return unexpected results if you are not aware of how they work.

There are blank cells in column C, shown in the picture above, that look empty but they are not. Column D shows what they actually contain and column E shows the character length of the content.

Cell C5 and C9 contain a formula that returns a blank, both the COUNTIF and the COUNTA function count those cells as non-empty.

Cell C8 has two space characters and cell C12 has one space character, column E reveals their existence by counting character length. The COUNTIF and the COUNTA function count those cells as non-empty as well.

## 4. Count not blank cells - SUMPRODUCT function

The following formula counts all non-empty values in cell range C3:C13 except formulas that return nothing. It checks if the values in cell range C3:C13 are not equal to nothing.

Formula in cell B16:

### 4.1 Explaining formula in cell B16

#### Step 1 - Check if cells are not empty

In this case, the logical expression counts cells that contain space characters but not formulas that return nothing.

The less than and the greater than characters are logical operators, the result are always boolean values.

C3:C13<>""

returns

{TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE}

#### Step 2 - Convert boolean values

The SUMPRODUCT function can't sum boolean values, we need to multiply with one to create an array containing 0's (zero) and 1's.

They are their numerical equivalents:

True = 1

FALSE = 0 (zero)

(C3:C13<>"")*1

becomes

{TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE}*1

and returns

{1; 1; 0; 1; 1; 1; 0; 1; 1; 1; 1}

#### Step 3 - Sum numbers

Why use the SUMPRODUCT function and not the SUM function? The SUMPRODUCT function can perform calculations in the arguments without the need to enter the formula as an array formula.

Array formulas are great but if possible avoid as much as you can. Excel 365 users don't have this problem, dynamic array formulas are entered as regular formulas.

SUMPRODUCT((C3:C13<>"")*1)

becomes

SUMPRODUCT({1; 1; 0; 1; 1; 1; 0; 1; 1; 1; 1})

and returns 9 in cell B16.

## 5. Regard formulas that return nothing to be blank and space characters to also be blank

The formula above in cell C16 counts only non-empty values, it considers formulas that return nothing to be blank and space characters to also be blank. This is made possible by the TRIM function that removes leading and ending space characters.

### 5.1 Explaining formula in cell B16

#### Step 1 - Remove space characters

TRIM(C3:C13)

returns

{"Green"; "Blue"; ""; "Red"; "Cyan"; ""; ""; "Yellow"; "Orange"; ""; "Brown"}

#### Step 2 - Identify not blank cells

TRIM(C3:C13)<>""

becomes

{"Green"; "Blue"; ""; "Red"; "Cyan"; ""; ""; "Yellow"; "Orange"; ""; "Brown"}<>""

and returns

{TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE}

#### Step 3 - Multiply with 1

TRIM(C3:C13)<>"")*1

becomes

{TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE}*1

and returns

{1; 1; 0; 1; 1; 0; 0; 1; 1; 0; 1}

#### Step 4 - Sum numbers in array

SUMPRODUCT((TRIM(C3:C13)<>"")*1)

becomes

SUMPRODUCT({1; 1; 0; 1; 1; 0; 0; 1; 1; 0; 1})

and returns 7.

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

How to create a dynamic pivot table and refresh automatically

This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]

Create a drop down list containing alphabetically sorted values

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

How to use the COUNTA function

The COUNTA function counts the non-empty or blank cells in a cell reference. The picture above demonstrates the COUNTA function […]

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Extract shared values between two columns

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

Use IF + COUNTIF to evaluate multiple conditions

The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula […]

This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]

### One Response to “How to use the COUNTIF function to count not blank cells”

### Leave a Reply to laurence

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

HI Oscar

this formula is not working for me on office 19

=SUMPRODUCT((C3:C13"")*1)

A1 has a 94 in the cell

B1 is blank (the lookup formula for this particular set of data is returning a blank for now)

C1 has the letter "X" inside cell

the count answer i'm looking for is 2 yet it still counts the blank lookup formula cell and returns 3

tried every combination of if, countif, counta etc just cant crack it, Thxs in advance.