On the "Year-to-Date Sales" worksheet, in cells L5:L15, enter a formula that returns the average "Total Sales" for the region in cell J5 and the "Representative" in cell K5.

Explanation: AVERAGEIFS Formula for Multi-Criteria Averaging --- Overall Goal The task asks you to calculate the average Total Sales for rows that match two conditions simultaneously: a specific region AND a specific sales representative. This requires AVERAGEIFS - not AVERAGE or

Create advanced formulas and macros

Question

On the "Year-to-Date Sales" worksheet, in cells L5:L15, enter a formula that returns the average "Total Sales" for the region in cell J5 and the "Representative" in cell K5.

Explanation

Explanation: AVERAGEIFS Formula for Multi-Criteria Averaging

Overall Goal

The task asks you to calculate the average Total Sales for rows that match two conditions simultaneously: a specific region AND a specific sales representative. This requires AVERAGEIFS - not AVERAGE or AVERAGEIF - because you have multiple criteria.

The Correct Formula

=AVERAGEIFS([TotalSales_range], [Region_range], J5, [Rep_range], K5)

A realistic example (assuming standard column layout):

=AVERAGEIFS($E$2:$E$100, $C$2:$C$100, J5, $D$2:$D$100, K5)

Step-by-Step Breakdown

1. Use AVERAGEIFS (not AVERAGEIF)

AVERAGEIF only accepts one condition. Since you need to match both region and representative, AVERAGEIFS is required. Using AVERAGEIF here would ignore one of the two criteria and return incorrect results.

2. First argument - the average range (Total Sales column, absolute)

This is the column being averaged. It must be locked with $ (e.g., $E$2:$E$100) so the range doesn't shift when the formula is copied down L5:L15. Skipping absolute references causes the range to drift row-by-row, averaging the wrong data.

3. Second argument - first criteria range (Region column, absolute)

The column containing region labels, also locked. AVERAGEIFS scans this column to find rows matching your region criterion.

4. Third argument - first criterion: J5 (relative reference)

This is the region value to match. Using a relative reference (not $J$5) is critical: when the formula copies from L5 down to L15, J5 becomes J6, J7, etc., dynamically picking up each row's region. If you locked it as $J$5, every row in L5:L15 would use the same region - almost certainly wrong.

5. Fourth argument - second criteria range (Representative column, absolute)

Same logic as the Region column - locked so it doesn't shift.

6. Fifth argument - second criterion: K5 (relative reference)

Same logic as J5. Relative so it adjusts to K6, K7... as the formula fills down.

What Goes Wrong If You Skip a Step

Mistake	Consequence
Use `AVERAGEIF` instead of `AVERAGEIFS`	Only one criterion applied; wrong averages
Forget `$` on data ranges	Ranges drift down; formula references wrong rows
Lock `J5` as `$J$5`	All 11 rows calculate average for the same region
Swap criteria range and criterion	Excel throws `#VALUE!` or matches nothing

Memory Tip

Think of AVERAGEIFS as: "Average this, where that equals X, and that other thing equals Y."

The structure always follows this pattern:

AVERAGEIFS(what_to_average, where_to_look_1, what_to_match_1, where_to_look_2, what_to_match_2)

Lock the data source ranges (they never move), leave the criterion cells relative (they should move with each row).

Topics

#Formulas#AVERAGEIFS#Multiple Criteria#Conditional Aggregation

Community Discussion

No community discussion yet for this question.

Full MO-201 Practice