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

## What is Countif?

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.

This function is capable of counting date formats, numbers, text values, blanks, not blanks, or lookups like cells containing specific characters, etc. In a nutshell, it is a function to count the number of cells that specify a condition.

In this article, we are going to talk about the ‘Not Blank’ function of COUNTIF. As the name suggests, COUNTIF with Not Blank criteria in spreadsheets is used to count non-empty cells in a column. In this function, the cells with 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 is basically filtering 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,"<>")`

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,"<>")`

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,"<>")`

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)`

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)`

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")`

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,"<>")`

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.

### 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)`

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)`

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.

### 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`

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.

## 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.

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)`

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.

### 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.

Expert Tip

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

If the issue is with your Computer or a Laptop you should try using Restoro which can scan the repositories and replace corrupt and missing files. This works in most cases, where the issue is originated due to a system corruption. You can download Restoro by clicking the Download button below.