How to Count cells that are not blank using Countif (2023)

Countif is a popular function used to count cells that are not empty. This function is compatible with all spreadsheet software like Excel, Google Sheets, or Numbers.

Count Cells that are not Blank using Countif
Count Cells that are not Blank using Countif

This function is capable of counting date formats, numbers, text values, blanks, non-blanks, or lookups, such as cells containing specific characters, etc. In a nutshell, it is a function that counts the number of cells meeting a specified condition.

In this article, we are going to discuss the ‘Not Blank’ function of COUNTIF. As the name suggests, COUNTIF with the Not Blank criterion in spreadsheets is used to count non-empty cells in a column. In this function, cells containing data are counted, whereas empty cells are excluded and not counted.

Formula of COUNTIF.

In the most basic form of the COUNTIF formula, you are required to specify the range and criteria. This formula essentially filters the number of cells in accordance with the criteria you mentioned.

=countif(range,criteria)

Countif using ‘Not Blank’ annotation

The following is the generic form of the Countif formula with not blank criteria:

=countif(range,"<>")
Countif Not Blank Formula
Countif Not Blank Formula

In this formula, we are specifying Countif to count all the cells in the given range that are not equal to nothing (represented by the <> symbol).

Example # 1: Single Column

In the image below we have two columns. Month, and Budget. To find filled cells in the Budget column (C4 to C15), you can use this formula:

=countif(C4:C15,"<>")
Counting Blank Cells in the Range C4 to C15
Counting Blank Cells in the Range C4 to C15

Now the result will come as 7, showing that there are 7 not blank cells in the given range.

Example # 2: Multiple Columns

To find cells that are not empty in both of these columns (B4 to C15), you have to enter the following formula:

=countif(B4:C15,"<>")
Counting Blank Cells in the Range B4 to C15
Counting Blank Cells in the Range B4 to C15

And the result will come out as 19, showing that there are 19 non-empty cells in the range.

Introducing CountA Function

There is an alternative function that you can use to count non-empty cells in a range:

=COUNTA(B4:C15)
Using the CountA Formula to Count Non-Blank Cells
Using the CountA Formula to Count Non-Blank Cells

Which shows the result of 19, the same as the Countif Not Blank function.

A point to remember is that the CountA function cannot accept more than one argument. The Countif function will be better to use if you are planning to use other arguments with the data.

The benefit of CountA: Multiple ranges

CountA function has an edge over the Countif function where it can incorporate multiple ranges. For example, if you want to count non-empty cells on multiple ranges in the dataset, then CountA can come in handy.

For example, enter the following formula in the I5 cell:

=COUNTA(B4:C15,D4:H5)
Using Multiple Ranges in the CountA Formula
Using Multiple Ranges in the CountA Formula

This shows the result of 25 which has two different ranges i.e., B4:C15 and D4:H5 range.

Using Countif for Multiple Ranges and Criteria

Countif can still be used for multiple ranges but it is a little more complicated than CountA. 

For example, look at the following formula in the J4 column:

=COUNTIFS(B4:B15,"<>"&"",C4:C15,"<10000")
Use Multiple Criteria With CountIFs Formula
Use Multiple Criteria With CountIFs Formula

This formula will count cells in the given ranges where cells are not blank and less than 10000, which is 6.  If you want to exclude zeros from the count while counting for non-blank, you can use the following formula:

=COUNTIFS(A1:A10,"<>0",A1:A10,"<>")

If you want to count the cells that are not blank adjacent to a particular cell, you can try the following:

=COUNTIFS(A:A,"B",B:B,">0")

A point to remember is that Countifs only counts the values that meet all the criteria. You can also use the DCountA function to calculate non-blank cells in a field given specific criteria.

Use Multiple Countif Functions

If you are not confident with Countifs or it does not work, you may use multiple Countif functions to achieve the same. Look at the formula below:

=(COUNTIF(B4:B15,"<>")+COUNTIF(C4:C15,"<>")+COUNTIF(D4:D15,"<>"))

This formula counts all the blank cells in the three different ranges. You can also use different criteria for different Countif functions.

Problem 1: The Invisible Non-Blank Cells

The issue with the Countif, Countifs, and CountA functions is that they will calculate cells that hold spaces, empty strings, or apostrophe (‘).

This can make the count wrong and the decisions made on that data will also be wrong. This is one of the most common problems with these formulas. You can better understand it by the following formula and image:

=COUNTIF(B4:C15,"<>")
Non-Visible Non-Blank Cells Problem
Non-Visible Non-Blank Cells Problem

Now, in the image, you can see that the total cells in B4:C15 are 24.

The blank cells shown in the image are 5 (C6, C9, C11, C13, and C14). So, the non-blank cells should be 19 (24-5) but the result in D4 is showing 20.

It is 20 because cell C13 has a space in it and the formula is also counting it as non-blank. 

Space in Cell C13 Making the Count of Non-Blank Cells Wrong
Space in Cell C13 Making the Count of Non-Blank Cells Wrong

Step 1: Find Invisible Non-Blank Cells Through the ‘Length’ Formula

In the above example, cell C13 has a space character in it. 

We can find it out using the Length formula. Following the above example, enter the following formula in D4:

=LEN(C4)
Finding the Cell With a Space by Using the LEN Formula
Finding the Cell With a Space by Using the LEN Formula

Now copy the formula up to the D15 cell. After that, you will notice the D13 cell shows 1 character but the C13 is not showing any character which shows us that there is an invisible character in the C13 cell.

Now select the C13 cell and press Delete. You will notice that the D4 cell shows 19 as non-blank cells, which is the correct answer.

Step 2: Verifying the Non-Blank Count

We can confirm the final count of non-blank cells by counting the blanks and comparing it with the total size of the dataset.

First, here is the formula to count blanks by using the Countif:

=COUNTIF(B4:C15,"")

This shows the result of 5 in cell G4. You can also use the =COUNTBLANK(B4:C15) formula.

Now count the total number of cells in the range by the following:

=ROWS(B4:C15)*COLUMNS(B4:C15)
Confirming the Countif Not Blank Result
Confirming the Countif Not Blank Result

This shows the result of 24 in cell H4.

Now we can confirm that the Countif with the ‘non-blank’ parameter shows the correct number of cells as 19.

24 - 5 = 19

Problem 2: The Hidden Apostrophe Problem

Like spaces in a cell, a hidden apostrophe is also not shown in the cell. We cannot use the length function, as the apostrophe is hidden, the Length function does not show the apostrophe as a character.

To understand the problem, look at the formula in cell D4 in the image below:

=COUNTIF(B4:C15,"<>")

The cell shows that there are 20 non-empty cells but we already know (from the example discussed earlier) it has 19 not blank cells.

Now, let us try the length formula but that shows characters 0 for all the blank cells.

Countif Blank Showing Incorrect Result Whereas Length Formula Showing Length of Empty Cells as Zero
Countif Blank Showing Incorrect Result Whereas Length Formula Showing Length of Empty Cells as Zero

Solution: Use Multiply by 1 to Find the Hidden Apostrophe

We can make use of the format of the apostrophe to find out whether there is one hiding in a cell. Since this is a text value, multiplying it with 1 will cause a value error

 In cell F4, enter the following formula:

=C4*1
Set a Multiply by 1 Formula in F4 Cell
Set a Multiply by 1 Formula in F4 Cell

Now copy the formula up to the F15 cell. Then you will notice a #value error in the F9 cell.

Now select the C9 cell and you will notice a hidden apostrophe in the formula bar.

Press the delete button and the result in the D4 cell will be shown as 19, which is the correct answer as per our previous findings.

Copy the Multiply by 1 Formula to Other Cells and Value Error Due to Apostrophe in C9 Cell
Copy the Multiply by 1 Formula to Other Cells and Value Error Due to Apostrophe in C9 Cell

Problem 3: The Empty String (=””) Problem

Like spaces and apostrophes, the empty string (=””) is also not shown in a cell. 

The Length function will not show the length of the Empty string but the Multiply by 1 method as discussed above in the Hidden apostrophe section works.

To better understand the problem, we will continue with the example discussed above. In the C14 cell, enter the following formula:

=""

Now you will notice that the output of Countif not blank has increased by 1 and has become 20 but the C14 cell is visibly blank. Now, enter the following formula in the cell E4:

=LEN(C4)

Then copy the formula to the E15 cell but the cell E14 is showing zero characters i.e., it is not considering that the empty string has a character but the Countif is counting the cell as non-blank.

Solution: Use Multiply by 1 to find the Empty String

Now enter the following formula in the F4 cell:

=C4*1

Then copy the formula up to the cell F15 and you will immediately notice that the F14 cell has shown a #value error.

Now select the C4 cell and you will notice a empty string (=””) in the formula bar of the spreadsheet application.

Finding Empty String Cell by Multiplying by 1 Formula
Finding Empty String Cell by Multiplying by 1 Formula

Now delete the empty string from the F14 cell and the Countif not blank formula in the D4 cell is now showing the exact result of 19.

You can also use the Multiply by 1 method to find the cells with spaces as well.

Workaround for all problems: Using SUMPRODUCT 

The above workarounds to solve the data inconsistencies are very efficient but can prove to be too long when you are dealing with huge data sets. In the example below, we have used the same problem as before and cells contain hidden values (empty strings, and hidden apostrophes)

To overcome, this manual work, enter the following formula in the F4 cell which uses SUMPRODUCT:

=SUMPRODUCT((TRIM(B4:C15)<>"")*1)
Sumproduct Function Showing the Correct Answer While Countif Not Blank Showing Incorrect Result Due to Invisible Non-Blank Cells
Sumproduct Function Showing the Correct Answer While Countif Not Blank Showing Incorrect Result Due to Invisible Non-Blank Cells

Now, you will see the F4 cell showing the result of 19, the actual result of non-blank cells in the given range as we have found earlier.

  • In this formula, TRIM(B4:C15) is used to remove spaces from the output.
  • TRIM(B4:C15)<>”” is used to identify that the cells are not blank.
  • Then (TRIM(B4:C15)<>””)*1 converts the Boolean output (True for not blank, False for blank) into their algebraic counterpart i.e., 1 for True and 0 for false.
  • Now the SUMPRODUCT will multiply and sum arrays, resulting in 19 here.

If that does not fulfill the requirements, then you may convert your data to a table and use the more structured formulas to easily count not blanks.

ABOUT THE AUTHOR

Kamil Anwar


Kamil is a certified MCITP, CCNA (W), CCNA (S) and a former British Computer Society Member with over 9 years of experience Configuring, Deploying and Managing Switches, Firewalls and Domain Controllers also an old-school still active on FreeNode.