This post will guide you how to count the number of cells with non-zero value with formula in Excel 2013/2016/2019/365.

For example, suppose we use a spreadsheet to calculate a time-sheet with a 0 value representing the absence of the day. Calculating the total number of working days is equivalent to calculating all cells in the spreadsheet that do not contain a value of 0.

1. Count the Number of Cells with non-zero value

Assuming that you have a list of data in range A1:C6 which contain text string values. And you want to count the number of cells with non-zero value in your data. You can use a formula based on the COUNTIF function to accomplish the result. The formula is like below:

=COUNTIF(A1:C6,"<>0")

Type this formula into a blank cell and press Enter key on your keyboard, you would get the total number of non-zero values in the given range of cells.

In this example, The countif function counts number of cells with specified condition. In this formula, the range reference is A1:C6, the condition is the logical test “not equal to 0”

2. Count the Number of Cells Ignoring Zeros and Blank Cells

The above formula can only be used to count the number of cells ignoring zero values, and if you also want to ignore blank cells at that same time while counting the cells in the given range of cell, you can use another formula based on the COUNTIF function and the COUNTA function. Just like below:

=COUNTA(A1:C6)-COUNTIF(A1:C6,"=0")

Type this formula into a blank cell and press Enter key on your keyboard.

The COUNTA function counts the numbers of cells that are not empty. And Minus the number of cells that have a 0 value. This way, no zeros and blank cells are counted.

3. Video: Count the Number of Cells with non-zero value

This video will show you two formulas for calculating cell numbers that have only non-zero values.

4. Related Functions

• Excel COUNTIF function
  The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel COUNTA function
  The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…