AVERAGEIFS that skips blanks and zeros

How it works, step by step

1. 01$D$2:$D$2000 is the average range, the Deal size column.
2. 02$B$2:$B$2000,H2 keeps only deals owned by the rep in H2.
3. 03$D$2:$D$2000,"<>0" applies a second test to Deal size itself and drops any row where the value is 0.
4. 04AVERAGEIFS ignores empty cells in the average range without any extra condition, so unpriced deals left blank do not pull the average down.
5. 05The result is the mean of the remaining values, for example 18,400 across 23 priced deals.

The same logic in four tools

=AVERAGEIFS($D$2:$D$2000,$B$2:$B$2000,H2,$D$2:$D$2000,"<>0")

=AVERAGEIFS($D$2:$D$2000,$B$2:$B$2000,H2,$D$2:$D$2000,"<>0")

SELECT AVG(deal_size) AS avg_deal_size
FROM deals
WHERE sales_rep = 'Dana Lee'
  AND deal_size <> 0;

import pandas as pd

rep = deals[deals["sales_rep"] == "Dana Lee"]
avg_deal_size = rep.loc[rep["deal_size"] != 0, "deal_size"].mean()

Mistakes that break it

• !If no rows pass the conditions, AVERAGEIFS returns #DIV/0!. Wrap it in IFERROR(...,"") for reps with no priced deals.
• !Plain AVERAGE counts zeros but skips blanks. Deals of 0 and 20,000 average to 10,000, while a blank and 20,000 average to 20,000.
• !In SQL, AVG skips NULL but includes 0. If unpriced deals are stored as 0 instead of NULL, the deal_size <> 0 filter is required.
• !pandas mean() skips NaN by default. Deal sizes read from a CSV as strings like "18,400" are object dtype and need pd.to_numeric(deals["deal_size"].str.replace(",", "")) before averaging.

Common questions

How do I average without zeros in Excel?

Use =AVERAGEIF(D2:D2000,"<>0"). It skips zeros, and blank cells are already ignored.

Does AVERAGEIFS ignore blank cells?

Yes. Empty cells in the average range are skipped and do not count toward the divisor. Zeros are included unless you add a "<>0" condition.

Why does AVERAGEIFS return #DIV/0!?

No rows met every condition, so there was nothing to average. Check the criteria for typos or extra spaces, and wrap the formula in IFERROR if an empty result is expected.