Calculate an Average by Category

Excel & Google Sheets

How it works

AVERAGEIF scans column A for rows equal to Sales and averages the matching rows' values from column C — everything else is ignored. The arguments read: where to look, what to match, what to average. For multiple conditions at once (region AND quarter), step up to AVERAGEIFS, which takes the average range first and then range/condition pairs, mirroring SUMIFS.

A:A

The category column to test.

"Sales"

The category to match. Point at a cell instead to make it reusable: E2.

C:C

The numbers to average for matching rows.

When to use it

Use it for per-group metrics: average deal size by region, average order value by customer type, average hours by project. For a full table of every category at once, a pivot table is usually faster.

Common mistakes

• ✕No rows match the condition.

  ✓AVERAGEIF returns #DIV/0! when nothing matches — a typo like "Sale" is the usual cause. Wrap with IFERROR if empty categories are expected.

• ✕Averaging a column that holds text numbers.

  ✓Cells like "3,000" stored as text are skipped silently, shifting the average. Check with ISNUMBER and convert with VALUE.