Basic Syntax and Differences

TIP: Keep ranges the same size, and use clear number and date formats.

One Criterion — AVERAGEIF

Example 1) Average unit price for the “Keyboard” category

=AVERAGEIF(A2:A100, "Keyboard", C2:C100)

Example 2) Average excluding zeros

=AVERAGEIF(C2:C100, "<>0")

Example 3) Exclude both blank cells and zeros

=AVERAGEIFS(C2:C100, C2:C100, "<>", C2:C100, "<>0")

Multiple Criteria — AVERAGEIFS

Example 4) Region=Seoul AND Product=Keyboard

=AVERAGEIFS(C2:C100, A2:A100,"Keyboard", B2:B100,"Seoul")

Example 5) OR criteria (Keyboard or Mouse)

=AVERAGE( FILTER(C2:C100, (A2:A100="Keyboard") + (A2:A100="Mouse") ) )  // 365

In earlier versions, combine the results of two AVERAGEIFS formulas with AVERAGE, or calculate directly with SUM/COUNT.

Average for a Date Range

Example 6) Average sales from 2025-08-01 to 2025-08-31

=AVERAGEIFS(C2:C100, B2:B100, ">="&DATE(2025,8,1), B2:B100, "<="&DATE(2025,8,31))

Example 7) Average for the reference month in F2

=AVERAGEIFS(C:C, B:B, ">="&EOMONTH(F2,-1)+1, B:B, "<="&EOMONTH(F2,0))

Include or Exclude Text and Use Wildcards

Example 8) Average for products containing "Pro"

=AVERAGEIFS(C2:C100, A2:A100, "*Pro*")

Example 9) Exclude "Refurbished"

=AVERAGEIFS(C2:C100, D2:D100, "<>*Refurbished*")

Weighted Average (SUMPRODUCT)

For a weighted average that uses quantity as the weight, SUMPRODUCT is more accurate than AVERAGEIF/S.

Example 10) Weighted average unit price for Region=Seoul (weight=quantity)

=SUMPRODUCT( unit price*quantity*(region="Seoul") ) / SUMPRODUCT( quantity*(region="Seoul") )

Example 11) Weighted average with multiple criteria

=SUMPRODUCT(unit price*quantity*(region="Seoul")*(product="Keyboard"))
 /SUMPRODUCT(quantity*(region="Seoul")*(product="Keyboard"))

Outlier Removal (TRIMMEAN)

If extreme values distort the average, use TRIMMEAN to remove the highest and lowest p% of values.

Example 12) Average excluding the top and bottom 10%

=TRIMMEAN(C2:C100, 0.10)

For conditional outlier removal, first apply the condition with FILTER, then apply TRIMMEAN to the result (365).

=TRIMMEAN( FILTER(C2:C100, A2:A100="Keyboard"), 0.10 )

Check Mistakes, Performance, and Accuracy

• Numbers stored as text → Left-aligned values may be text. Convert them with VALUE or standardize the source format.
• Zero denominator → If no values meet the criteria, you get #DIV/0!. Use IFERROR(formula,"None").
• Zeros and blank cells → They are treated differently in averages. If needed, use AVERAGEIFS(range,range,"<>",range,"<>0").
• Mismatched range sizes → All ranges must have the same number of rows and columns.
• Performance → Use table references and LET to cache repeated calculations.

Summary

FAQ