How to count non-zero values in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B11,"<>0",B2:B11,"<>")Exclude zeros and blanks in one COUNTIFS: =COUNTIFS(B2:B11,"<>0",B2:B11,"<>").
- Result on the example
- 5 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
"<>0" keeps cells that aren't zero, but an empty cell isn't zero either, so on its own it would count the two days with no data. The second condition, "<>", keeps only non-empty cells. Together they count the five days that sold something.
=COUNTIFS(B2:B11,"<>0",B2:B11,"<>")Result5
B2:B1110 rows × 1 col: 12, 0, 8, (empty), …A range: 10 cells in one column.
COUNTIFS(B2:B11,"<>0",B2:B11,"<>")5COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1110 rows × 1 col: 12, 0, 8, (empty), … - criteria1
"<>0" - criteria_range2
B2:B1110 rows × 1 col: 12, 0, 8, (empty), … - criteria2
"<>"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B11,"<>0",B2:B11,"<>"). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF(B2:B11,">0") | 5 | Simpler when values can't be negative: it skips zeros, blanks and text. |
| =COUNTIF(B2:B11,"<>0") | 7Different result | The common first attempt. Returns 7: it also counts the two blank days. |
Common mistakes
"<>0"counts blanks and text, because they are "not zero". Add the"<>"condition or use">0".- Zeros hidden by a number format (showing as - or blank) still count as zeros, which is what you want here.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count non-zero values
E1, count the days in B2:B11 with a sales figure other than zero.Questions
How do I average only the non-zero values?
Use AVERAGEIF: =AVERAGEIF(B2:B11,"<>0"). It ignores blanks on its own.
How do I count zeros?
Use =COUNTIF(B2:B11,0); blanks are not counted as zeros.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.