# How to fix #DIV/0! in AVERAGEIF

Check that something matches before averaging: =IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7)).

**Excel:** `=IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Region | Amount |  | Region | Average sale |
| 2026-10-01 | East | 1200 |  | East |  |
| 2026-10-02 | West | 850 |  | West |  |
| 2026-10-05 | East | 430 |  | North |  |
| 2026-10-07 | West | 990 |  | South |  |
| 2026-10-08 | East | 615 |  |  |  |
| 2026-10-12 | North | 305 |  |  |  |

Formula in F2, filled to F5: `=IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7))`

Result: $748; $920; $305; No sales

## How it works

An average divides a total by a count. South has no sales, so the count is 0 and AVERAGEIF returns #DIV/0!. Checking the count with COUNTIF first returns a message for that case and the average for the rest.

## Other ways

- `=IFERROR(AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7),"No sales")`: Shorter, but it would also hide a mistyped range.
- `=IFERROR(AVERAGE(FILTER($C$2:$C$7,$B$2:$B$7=E2)),"No sales")`: Excel 365 and Google Sheets: FILTER the matching amounts, then average them.

## Common mistakes

- Numbers stored as text aren't averaged either, so a column of text numbers also gives #DIV/0!.
- Returning 0 instead of a message makes "no sales" look like sales of zero in charts and later averages.

## Questions

**Why does plain AVERAGE return #DIV/0!?**

The range has no numbers in it: it is empty, or everything in it is text.

**How do I average with several conditions?**

Use AVERAGEIFS, with a COUNTIFS check in front.

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/averageif-div0-error