How to Count the Number of Cells with Non-Zero Values in Excel

From this tutorial, you will know how to count the number of cells with non-zero values in Excel.

In Excel, we may sometimes need to count how many cells in a particular cell range have non-zero values. We might need to do that because we want to make sure how many of our data entries at least have something or we have other reasons. Whatever the reason is, we should understand what should we do so we can get the number of cells with the criterion we want.

If you want to do that kind of counting process but don’t know how then you have come to the right place! We will discuss the way to do it here so you can get the correct results you want from your Excel work.

Want to know what is the way to count the number of cells with non-zero values in Excel? Let’s get into it straight away.

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

How to Count the Number of Cells with Non-Zero Values in Excel

To count the number of non-zero cells in Excel, we can write a formula that does this function for us. In this formula, we will utilize the COUNTIF function as it is a function that can help us to count the number of cells in a cell range that fulfills a criterion.

Here is the writing syntax of the formula.



The first input here is the address of the cell range where we want to count the cells with non-zero values. Meanwhile, the second input is the criterion we should write in COUNTIF if we want a criterion of non-zero.

The <> symbol there represents “not equal to”. As we use a symbol that is considered text in Excel, we should enclose our criterion with double quotes ( “” ).

Here is an implementation example of the formula in Excel.



As you can see, we can get the number of non-zero cells from the data table on the left in the screenshot with the COUNTIF formula we have discussed. Just input the cell range that we want to count the number of non-zero values from in the formula and you will get the desired result.

How to Count the Number of Non-Blank Cells with Non-Zero Values in Excel

The COUNTIF formula we have discussed assumes that blank cells are also non-zero cells. What if we want to consider blank cells as cells with zero values too?

For that, we need to use the COUNT function to help us in our formula together with a COUNTIF that counts the number of cells with zeroes. Just calculate the difference between the COUNT and COUNTIF results and we will get the number of non-blank cells with non-zero values we want.



COUNT will count all the non-blank cells in our cell range that has numbers as their content while COUNTIF will count the cells with zero as their values. The difference between them should make us have the number of cells in our cell range that has non-zero values and aren’t blank.

We use COUNT here because the assumption is we only want to count cells that have numbers, not other types of data (text, logic values, etc). If you want to count cells with other types of data too, use COUNTA instead of COUNT (the input is still the cell range where we want to count the number of non-blank, non-zero cells).

As an example of the formula implementation, you can see the screenshot below.



You can see there that the combination of COUNT and COUNTIF in our formula helps us to get the number of non-blank cells with non-zero values in our cell range. If we use the COUNTIF formula we discussed in the previous part of this tutorial, we will count the blank cells too.



So, which formula you should use? That, of course, depends on the kind of result you want to get. Choose wisely so you don’t get wrong results from your work in Excel.

Exercise

As you have learned how to count non-zero cells in Excel, now is the time to practice the lessons.

Download the exercise file below and do the following instructions. See the answer key file if you have done the exercise and want to check your answers or if you feel confused when you try to finish the exercise.

Link to the exercise file:
Download here

Questions

Do each instruction in the appropriate gray-colored cell according to the instruction number!

1. Count the number of regions with 0 branches! Assume that blank cells don’t have zero values
2. Count the number of regions with 0 branches! Assume that blank cells have zero values
3. Count the number of regions with 0 branches! Assume that the region where a branch will be opened soon does have a branch

Link to the answer key file:
Download here

Additional Note

You can also use COUNTIFS instead of COUNTIF in the formulas we have discussed. COUNTIFS can accommodate the counting process with only one criterion as well besides the one with multiple criteria.



Excel tutorials you might want to read too: