# How to count non-zero values in Excel

Exclude zeros and blanks in one COUNTIFS: `=COUNTIFS(B2:B11,"<>0",B2:B11,"<>")`.

**Excel:** `=COUNTIFS(B2:B11,"<>0",B2:B11,"<>")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Day | Units sold |  | Days with sales |
| Mon | 12 |  |  |
| Tue | 0 |  |  |
| Wed | 8 |  |  |
| Thu |  |  |  |
| Fri | 15 |  |  |
| Sat | 0 |  |  |
| Sun | 22 |  |  |
| Mon | 3 |  |  |
| Tue |  |  |  |
| Wed | 0 |  |  |

Formula in E1: `=COUNTIFS(B2:B11,"<>0",B2:B11,"<>")`

Result: 5

## 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.

## Other ways

- `=COUNTIF(B2:B11,">0")`: Simpler when values can't be negative: it skips zeros, blanks and text.
- `=COUNTIF(B2:B11,"<>0")`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-non-zero-values