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

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

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

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