Count Cells That Are Not Blank in Excel (6 Useful Methods)

Sometimes for better visualization or any other reasons, your workbook may have blank cells. But in the calculation process or monitoring data, you have to count only not blank or non-empty cells.

In this article, I will focus on 6 useful methods to count cells that are not blank in Excel.

Download Practice Workbook

How to Count Cells that are Not Blank in Excel

In the following illustration, we see vegetable Item and producing Country. But there exist some blank or empty cells. But we have to count not blank cells for calculation. And we can utilize the following methods.

Dataset to count cells that are not blank in excel

1. COUNTA Function

The COUNTA function shows the number of cells that are not empty. It also considers error values, text, numbers, formula, empty texts (= “”) in counting the number of cells. The syntax of the function is:

=COUNTA (value1, [value2], …)

Here, the arguments may be cell references, items or cell ranges.

Steps:

  • Select a blank cell like F7
  • Type the formula =COUNTA (B4:C16) where B4 is the value1 and C16 is the value2.
  • Press Enter

COUNTA function to count cells that are not blank in excel

2. COUNTIF Function

The COUNTIF function counts the number of cells that match the single condition. Counting cells with dates, numbers, and texts are used by the COUNTIF function. More importantly, this function ignores formulas though the COUNTA function counts cells containing any formula. The COUNTIF function syntax is

=COUNTIF (range, criteria) 

Here, range refers to the range of cells and criteria means any condition.

If you want to count not blank cells with numbers or any types of value just follow the process.

Steps:

  • Select a blank cell like G7
  • Type the formula: =COUNTIF(B4:D16, "<>"&"") where B4 to D16 is the cell range and “<>”&”” are criteria.
  • Press Enter

COUNTIF function to count cells that are not blank in excel

3. COUNTIFS Function

If you are looking to apply multiple criteria for a corresponding range, then COUNTIFS will be the right choice. Though we are talking about counting non blank cells only, you can apply the function in the dataset. The syntax is:

=COUNTIFS (range1, criteria1, [range2], [criteria2], …) 

Here range1, range2 refers to first and second cell range respectively and criteria1, criteria2 refers to conditions for first and second cell range.

Steps: 

  • Select a blank cell namely F7
  • Type the formula i.e. =COUNTIFS(B4:C16,"<>") 
  • Press Enter

COUNTIFS function to count cells that are not blank in excel

4. COUNTBLANK Function

Well, the COUNTBLANK function is mainly used for counting blank or empty cells in range. But you can easily count non blank cells by subtraction from the product of ROWS and COLUMNS. ROWS and COLUMNS return the number of rows and columns in a reference or array. The syntax of three functions are

=COUNTBLANK(range) 
=ROWS(Array) 
=COLUMNS(Array) 

Here, range and array mean the cell range basically.

In the following dataset, there are some blank cells. Using the function, we will get the not blank cells.

Steps: 

  • Select a blank cell like F7
  • Type the formula viz. =ROWS(B4:C16)*COLUMNS(B4:C16)-COUNTBLANK(B4:C16) where B4 to C16 is the cell range
  • Press Enter

COUNTBLANK function to count cells that are not blank in excel

5. SUMPRODUCT Function

COUNTA and COUNTIF consider any empty text, formula, error values, etc., as not blank cells, but the cell looks blank. For example, the C9 cell in the following dataset has no appearance though = “” etc. are available in the cell. In that case, using SUMPRODUCT might be effective

SUMPRODUCT function to count cells that are not blank in excel

The syntax of the SUMPRODUCT function is

=SUMPRODUCT(array1, [array2], [array3], …) 

Here array refers to the cell range that you want to multiply and add.

We can apply the function for our dataset like the following

Steps:

  • Select a blank cell like F9
  • Type the formula i.e. =SUMPRODUCT((B4:C16<>"")*1) where B4 to C16 is the cell range, the logical expression counts cells that contain space characters but not formulas and empty texts that return nothing.
  • Press Enter

SUMPRODUCT function to count cells that are not blank in excel

The output shows that the number of not blank cells using SUMPRODUCT is 20 while for COUNTA is 21 that means C9 is not counted in the case of SUMPRODUCT.

The same output will be found using a combination of SUMPRODUCT and LEN functions. LEN can count all special characters, non-printable characters and all spaces from an excel cell. The syntax of LEN is

=LEN(text)  

The LEN function finds the number of characters for each cell in the range, therefore SUMPRODUCT counts the cells that contain at least one character.

The SUMPRODUCT then returns an array with True and False. The ‘–‘ converts the True to 1 and False to 0. The SUMPRODUCT then sums the values and returns the result.

Steps: 

  • Select a blank cell
  • Type the formula =SUMPRODUCT(--(LEN(B4:C16)>0))
  • Press Enter

SUMPRODUCT function to count cells that are not blank in excel

6. Find & Replace Tool

The standard Excel dialog Find and Replace allows you to count non blank cells as well. It is a good method in the case of a large dataset. Additionally, all the values and their cell addresses are shown in one window. Furthermore, by clicking its name in a list, you can easily navigate to any item. Now, you may proceed with the following steps.

Steps:

  • Select the cell range where you want to count blank cells
  • Pick the Find & Select dialog from Home Tab
  • Click on Find option

Note: You may use CTRL+F as a Shortcut Key for the above process

Find & Replace Tool to count cells that are not blank in excel

  • Type * mark in Find what option and chose to Find All option
  • You will get the 20 cells with the sheet name, cell name, and value.

Find & Replace Tool to count cells that are not blank in excel

Things to Keep in Mind

Be careful about the file name, the file location, and also about the extension file of Excel. Always follow the syntax during typing the formula in a cell. You have to be cautious about brackets.

Things to Keep in Mind

Above the massage shows the typo error in a formula

Conclusion

Hopefully, the above six methods might be fruitful for counting non-blank cells in excel. Thanks for reading this article. If you have any queries, please share below.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo