Average with Multiple Criteria in Excel: AVERAGEIFS Guide

This calculator works out the average of a set of numbers that meet multiple conditions at once. You enter the data range, the criteria ranges, and the conditions, and it returns the average of only the matching values. The result is the arithmetic mean of the filtered subset, shown with the same unit as your input data.

Formula

The calculator uses the same logic as Excel’s AVERAGEIFS function. In words: find all rows where every condition is true, then take the arithmetic mean of the values in the data range from those rows. The formula is:

Average = (1/n) × Σ x_i for all i where Condition1 and Condition2 are true

• x_i – each value in the data range (numeric only; text and blanks are ignored).
• Condition1, Condition2 – logical tests applied to the corresponding criteria ranges. Conditions can be numbers, text, or expressions like “>3” or “Yes”.
• n – the count of values that meet all conditions.

In the calculator above, the expression uses two conditions: one numeric (crit1 > 3) and one text (crit2 = “Yes”). The mean is computed only over rows where both hold.

Worked Example

Using the default values: data range = [10], criteria range 1 = [5], condition 1 = “>3”, criteria range 2 = [5], condition 2 = “Yes”. Since there is only one row, check if it meets both conditions: 5 > 3 is true, and 5 = “Yes” is false (a number never equals text). So no rows qualify, and the average is undefined. The calculator returns 0 (or blank) because there are no matches.

To see a meaningful result, imagine a small dataset:

• Data: 10, 20, 30, 40
• Criteria range 1 (Group): A, A, B, B
• Criteria range 2 (Passed): Yes, No, Yes, Yes

If you want the average of values where Group = “A” and Passed = “Yes”, only the first row (10) matches, so the average is 10. If you change the condition to Group = “B”, rows 3 and 4 match (30 and 40), giving an average of 35.

When to Use AVERAGEIFS vs AVERAGEIF

Use AVERAGEIFS when you need to filter by two or more criteria. Use AVERAGEIF when you have only one condition. The syntax also differs: AVERAGEIF puts the criteria range first, then the condition, then the average range. AVERAGEIFS puts the average range first, then pairs of criteria range and condition. This is a common source of errors when switching between the two.

For example, to average sales in the West region only, AVERAGEIF works. To average sales in the West region for the month of March, you need AVERAGEIFS.

Common Mistakes and How to Avoid Them

• Mismatched ranges: All criteria ranges must be the same size and shape as the average range. If they differ, Excel returns #VALUE! or an incorrect result.
• Using AND logic incorrectly: AVERAGEIFS applies AND logic across all conditions. If you need OR logic (e.g., average of sales in West OR East), you must use AVERAGEIF twice and combine the results manually, or use an array formula.
• Text vs numbers: A condition like “>3” works for numbers. For text, use “Yes” or “=Yes” (the equals sign is optional). For dates, use a date serial number or a date function like DATE(2024,1,1).
• Blank cells: Blank cells in the average range are ignored. Blank cells in criteria ranges are treated as empty text, so a condition of “” will match them.
• Wildcards: You can use * and ? in text conditions. For example, “A*” matches any text starting with A. To match a literal asterisk, use ~*.

How to Choose the Right Function in Excel

In Excel, the AVERAGEIFS function is built-in. The syntax is:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

You can have up to 127 pairs of criteria ranges and conditions. The function ignores cells that are empty or contain text in the average range, but it does not ignore errors – if any cell in the average range has an error, the whole function returns an error. To avoid that, you can wrap the range in IFERROR, but that is an array formula in older Excel versions.

Limitations and Alternatives

AVERAGEIFS cannot handle OR logic directly, cannot average based on the result of another calculation (like average of values above the median), and cannot use dynamic arrays in older Excel versions. For more complex conditions, consider:

• SUMPRODUCT: =SUMPRODUCT((range1="A")*(range2="Yes")*data)/SUMPRODUCT((range1="A")*(range2="Yes")) – works for OR logic and multiple conditions without array entry.
• Array formulas: In Excel 365, you can use FILTER and AVERAGE directly: =AVERAGE(FILTER(data,(range1="A")*(range2="Yes"))).
• PivotTables: For large datasets, a PivotTable with filters gives a visual way to average subsets.

Frequently Asked Questions

How do I use AVERAGEIFS with dates?

Use a date condition like “>”&DATE(2024,1,1) or reference a cell containing a date. For example, =AVERAGEIFS(sales, dates, “>”&A1) averages sales where the date in the dates range is after the date in A1.

Can I use AVERAGEIFS with text that contains a number?

Yes, but text conditions are case-insensitive. To match text exactly, use the condition without wildcards. For example, “123” matches cells containing the text 123, not the number 123.

Why does my AVERAGEIFS return #DIV/0!?

That error appears when no rows meet all conditions. The denominator (count of matches) is zero. Check your conditions for typos or mismatched ranges.

How do I average with OR logic in Excel?

Use SUMPRODUCT: =SUMPRODUCT(((range=”A”)+(range=”B”))*data)/SUMPRODUCT(((range=”A”)+(range=”B”))). This adds the two boolean arrays, counting rows that match either condition.

Does AVERAGEIFS ignore hidden rows?

No. AVERAGEIFS ignores only cells that do not meet criteria. To exclude hidden rows, use SUBTOTAL or a filtered table with AGGREGATE.

Can I use wildcards in AVERAGEIFS for numeric conditions?

No, wildcards only work with text. For numbers, use comparison operators like >, <, >=, <=.

Related

• Soul Split Katana Jujutsu Zero: NPC, Materials, Stats
• Autonomous Plant Irrigation with ESP32 or Arduino
• Add Task Factory Visual Studio: Setup Guide

in data | Data range (e.g., A2:A100) | - | 10 | 0
in crit1 | Criteria range 1 (e.g., B2:B100) | - | 5 | 0
in val1 | Condition 1 (e.g., ">3") | - | >3 | 0
in crit2 | Criteria range 2 (e.g., C2:C100) | - | 5 | 0
in val2 | Condition 2 (e.g., "Yes") | - | Yes | 0
out avg | Average of matches | - | mean(data * (crit1 > 3) * (crit2 = "Yes")) | 2