How to fix #DIV/0! in AVERAGEIF
Formula (Excel and Google Sheets)
=IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7))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)).
- Result on the example
- one result per row in
F2, filled down toF5 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F5. Change any input and the result updates, or edit the formula to experiment.
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.
=IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7))Result748.3333333
$B$2:$B$76 rows × 1 col: "East", "West", "East", "West", …A range: 6 cells in one column.
E2"East"The value in E2.
COUNTIF($B$2:$B$7,E2)3COUNTIF: Counts cells that meet one condition.- range
$B$2:$B$76 rows × 1 col: "East", "West", "East", "West", … - criteria
E2"East"
- range
COUNTIF($B$2:$B$7,E2)=0FALSEThe = operator tests whether the two sides are equal.
$C$2:$C$76 rows × 1 col: $1,200, $850, $430, $990, …A range: 6 cells in one column.
AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7)748.3333333AVERAGEIF: Average of values that meet a condition.- range
$B$2:$B$76 rows × 1 col: "East", "West", "East", "West", … - criteria
E2"East" - average_range
$C$2:$C$76 rows × 1 col: $1,200, $850, $430, $990, …
- range
IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7))748.3333333IF: Returns one value if a test is true, another if false.- logical_test
COUNTIF($B$2:$B$7,E2)=0FALSE - value_if_true
"No sales"not used - value_if_false
AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7)748.3333333
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(COUNTIF($B$2:$B$7,E2)=0,"No sales",AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IFERROR(AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7),"No sales") | $748, $920, $305… | Shorter, but it would also hide a mistyped range. |
| =IFERROR(AVERAGE(FILTER($C$2:$C$7,$B$2:$B$7=E2)),"No sales") | $748, $920, $305… | 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.
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.
Fix #DIV/0! in AVERAGEIF
F2, average the amounts for the region in E2, showing "No sales" instead of an error when the region has none. Fill down to F5.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.
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.