Count Cells Ignoring Zeros or Blank Cells in Excel

In Excel, there is always a possibility to include or exclude a particular type, text, or figure from the calculations.

We will show how you can use the COUNT formula but ignore cells with zeroes or blank cells from the calculation.

Count Cells Ignoring Zeroes or Blank Cells

For our example, we will use the list of random numbers, that will be inserted into columns A and B. In our range, we will not insert all the cells (we will leave them blank), and some of the cells will have 0 values:

If we would insert the COUNT formula in cell B12 and encompass our range (A2:B11):

We would get the following results:

As seen, the COUNT formula already omits the blank cells. If we would go by this logic, and simply use COUNTIF to exclude the zeroes with the formula:

And put this formula in cell B13, we will end up with the result:

This is still not the correct result, as the count of the cells in our range that do not have zero or are not empty is 13. We are aiming for this number, and number 17 is clearly incorrect.

To achieve our goal, we need to use the COUNTIFS formula. This formula basically counts the number of cells with two or more conditions or criteria. In our case, the formula in cell B14 will be:

The first criterion will be to exclude zeroes, and the second will be to exclude empty cells. Our range will be the same in both cases. End result will be:

We can also use the SUM and IF formulas in combination to achieve the same result. We will insert the following formula in cell B15:

This formula does the same thing as COUNTIFS does. The result is: