ExcelInsider Team
Sometimes, you need to count values that are not equal to zero. To do so, you don’t need to scan your worksheet manually; By using several useful functions, like COUNTIF, COUNTIFS, and SUMPRODUCT, you can determine the count of non-zero values. In this article, we will go through different step-by-step methods to count not equal to zero values in Excel.
To count the values which are not equal to zero using the COUNTIFS function, follow these steps below.
➤ Select cell E13 and write this formula: =COUNTIFS(E2:E11,”<>0″,E2:E11,”<>”)
➤ Press Enter and see the count of cells that are not equal to zero. The result is 5.
In this article, we will explore various methods that how we will count values that are not equal to zero. Counting with Blank Cells, Counting Without Blank Cells, Combining COUNT and COUNTIF for Numeric Non-Zero Counts, Using SUM with Logical Conditions to Count Non-Zero Values, Employing the SUMPRODUCT Function for Non-Zero Counting, Filtering Numeric Data with SUMPRODUCT and ISNUMBER, Counting Non-Zero Values with the FILTER Function and the COUNTA Function are ways to count values that are not equal to zero.
Counting with Blank Cells
Using the COUNTIF function, we can count values that are not equal to zero in Excel. After defining the cell range, the “< >0” condition will exclude all zeros from the count. This formula will also count the blank cells, but not count the zero values.
Let’s look at the dataset below, where the product sales values contain some zero values and blanks. We will count the non-zero values with the blank cells using the COUNTIF function.
Steps:
➤ Select cell E13 and write this formula
=COUNTIF(E2:E11,"<>0")
➤ Press Enter and see the count of cells that are not equal to zero and the blank cells. The result is 7.
Counting Without Blank Cells
In this method, we will use the COUNTIFS function to count the numeric values without blank cells and zero values. This function will exclude zero values and the blank cells by using the “<>”, and then it will only count meaningful numeric values.
Steps:
➤ Select cell E13 and write this formula
=COUNTIFS(E2:E11,"<>0",E2:E11,"<>")
➤ Press Enter and see the count of cells that are not equal to zero. The result is 7.
Combining COUNT and COUNTIF for Numeric Non-Zero Counts
Combining the COUNT function with the COUNTIF function allows for counting values that are not equal to zero. It will ignore text values and blank cells. It will only count the numeric cell values that are not equal to zero. The COUNT function will count all the numeric cells with zero values and blanks. After ignoring blanks, the zero values will be counted with the COUNTIF function.
Steps:
➤ Select cell E13 and write this formula
=COUNT(E2:E11) - COUNTIF(E2:E11,0)
➤ Press Enter and see the count of cells that are not equal to zero. The result is 5.
Using SUM with Logical Conditions to Count Non-Zero Values
By using the SUM function and the logical conditions (E2:E11<>0), we can count non-zero values. The logical conditions will find any non-zero values; they will return TRUE; if they find any zero values, otherwise they will return FALSE. All TRUE results will then be added up by the SUM function.
Steps:
➤ Select cell E13 and write this formula
=SUM(--(E2:E11<>0))
➤ Press Enter and see the count of cells that are not equal to zero. The result is 5.
Inserting the SUMPRODUCT Function for Non-Zero Counting
After setting a logical condition (E2:E11<>0) inside the SUMPRODUCT function, the logical condition will check each value. The non-zero value will be converted into 1, and zero values into 0. Then, it will sum up to count the total of non-zero values.
Steps:
➤ Select cell E13 and write this formula
=SUMPRODUCT(--(E2:E11<>0))
➤ Press Enter and see the count of cells that are not equal to zero. The result is 5.
Filtering Numeric Data with SUMPRODUCT and ISNUMBER
In this method, we will combine the SUMPRODUCT function with the ISNUMBER function. The ISNUMBER function will only count the numeric values, and the SUMPRODUCT function with the condition <>0 will count only the non-zero values without blank cells.
Steps:
➤ Select cell E13 and write this formula
=SUMPRODUCT(--ISNUMBER(E2:E11), --(E2:E11<>0))
➤ Press Enter and see the count of cells that are not equal to zero. The result is 5.
Counting Non-Zero Values with the FILTER and the COUNTA Functions
The FILTER function works with dynamic arrays. The FILTER function will filter the values that are not equal to zero, then the COUNTA function will count the non-zero values.
Steps:
➤ Select cell E13 and write this formula
=COUNTA(FILTER(E2:E11,(E2:E11<>0)*(E2:E11<>"")))
➤ Press Enter and see the count of cells that are not equal to zero. The result is 5.
Frequently Asked Questions
Is it possible to use COUNTIF to count non-zero values in both numbers and text?
The COUNTIF function will count only numeric values, not text values. By combining the COUNTIF function with the ISNUMBER function, you can count the text.
Can the COUNTIFS function handle other conditions, rather than counting non-zero values?
Yes! The COUNTIFS function can handle other conditions, rather than counting non-zero values.
Can I add new data if the calculations are finished?
Yes, the SUMPRODUCT, COUNTIF, and COUNTIFS functions will allow you to add new data after completing calculations.
Wrapping Up
In this article, we discussed seven different methods step by step on how you can count values that are not equal to zero in Excel. Hopefully, you have enjoyed the article. Feel free to download the practice file and share your thoughts and suggestions in the comment box.
