How to Calculate an Average by Group in Excel

To calculate an average by group in Excel, list each group once and put an AVERAGEIF formula next to it: =AVERAGEIF($B$2:$B$9,F2,$D$2:$D$9) returns the average for the group named in F2. Use AVERAGEIFS when you need a second condition. This guide is for anyone comparing teams, regions or products in a report or a job test, and it covers automatic group lists, empty groups and PivotTables too.

The sample data#

The examples use these training scores in A1:D9. Each employee belongs to one team and one site:

The overall average, =AVERAGE(D2:D9), is 81.5. The question is how each team compares with that.

How to average one group with AVERAGEIF#

AVERAGEIF averages the numbers in rows that match one condition. It has three arguments, in this order:

=AVERAGEIF(range, criteria, [average_range])

• range is the column to check, here the Team column.
• criteria is the group to match, such as "Sales".
• average_range is the column of numbers to average.

=AVERAGEIF(B2:B9,"Sales",D2:D9)

The result is 84: (84 + 90 + 78) / 3. AVERAGEIF ignores capital letters, so "sales" works too. The AVERAGEIF, MAXIFS and MINIFS lesson practises this function alongside its cousins for the highest and lowest values.

How to build an average by group table#

A group table has one row per group and one formula per row. Point the criteria at a cell so the same formula works for every group:

1. In F1 type Team and in G1 type Average.
2. In F2:F4 type Sales, Support and Finance.
3. In G2 type the formula below and fill it down (Windows: Ctrl+D; Mac: ⌘D).

=AVERAGEIF($B$2:$B$9,F2,$D$2:$D$9)

The $ signs lock both ranges, so only F2 changes as you fill down. Support's exact result is 72.666..., shown here with two decimals. To store a rounded value, wrap it: =ROUND(AVERAGEIF($B$2:$B$9,F2,$D$2:$D$9),1) returns 72.7.

This is the same layout as a sum by category or a count by category table. Put COUNTIF, SUMIF and AVERAGEIF side by side and you have a full summary per group.

List the groups automatically with UNIQUE#

In Excel 2021 or Microsoft 365, UNIQUE builds the list of groups for you. In F2, type:

=UNIQUE(B2:B9)

It spills Sales, Support and Finance into F2:F4, in the order they first appear. Then average every group with one formula in G2:

=AVERAGEIF(B2:B9,F2#,D2:D9)

The # refers to the whole spilled list, so when a new team appears in column B, both columns grow on their own. Use =SORT(UNIQUE(B2:B9)) for an A to Z list.

Average by group with two conditions (AVERAGEIFS)#

Use AVERAGEIFS when each row must match two or more conditions, such as team and site. Note the different order: the numbers to average come first, then pairs of ranges and criteria.

=AVERAGEIFS(D2:D9,B2:B9,"Sales",C2:C9,"London")

The result is 81: Aisha (84) and Farid (78). Chloe is in Sales but in Leeds, so she is left out. Support in Leeds gives 76.5 (72 and 81).

For a full grid, put teams down column F and sites across row 1, then use =AVERAGEIFS($D$2:$D$9,$B$2:$B$9,$F2,$C$2:$C$9,G$1) and fill right and down. The AVERAGEIFS function guide covers each argument. If you are unsure which of the two functions to use, the comparison of AVERAGEIF vs AVERAGEIFS explains the trade-offs, and the AVERAGEIFS guide has more condition examples.

Fix #DIV/0! for groups with no rows#

If no row matches a group, AVERAGEIF has nothing to divide by and returns #DIV/0!. For example, =AVERAGEIF(B2:B9,"Marketing",D2:D9) returns #DIV/0! because there is no Marketing team. Wrap the formula in IFERROR to show a message instead:

=IFERROR(AVERAGEIF($B$2:$B$9,F2,$D$2:$D$9),"No data")

Before you hide the error, check that it is really an empty group. A group name with an extra space, such as "Sales ", also matches nothing and gives the same error.

Zeros, blanks and text in the numbers#

AVERAGEIF and AVERAGEIFS treat empty cells and zeros differently, and that changes the result:

• Blank cells in the number column are skipped. If Dev's score were empty, Support's average would be (72 + 81) / 2 = 76.5.
• Zeros are included. If Dev's score were 0, Support's average would be (72 + 0 + 81) / 3 = 51.
• Text, such as "Absent", is skipped like a blank.

If a 0 means "no score yet" rather than a real zero, exclude it with a second condition on the same column: =AVERAGEIFS(D2:D9,B2:B9,"Support",D2:D9,"<>0"). The guide to averaging without zeros covers this in detail.

Average by group with a PivotTable#

A PivotTable gives averages by group without formulas:

1. Click a cell in the data and go to Insert > PivotTable, then click OK.
2. Drag Team to Rows and Score to Values.
3. The value shows Sum of Score. Click it, choose Value Field Settings, pick Average and click OK.

Drag Site to Columns for the team-by-site grid. Remember to right-click and Refresh after the data changes. Formulas update on their own and are what most tests check, so the pivot-style summary lesson teaches the formula version.

Practise it#

Try the exercise on this page: it averages one team at one site with AVERAGEIFS. Then practise AVERAGEIF, MAXIFS and MINIFS in a live spreadsheet, where you type each formula and it checks it instantly, and use the pivot-style summary lesson to build a full table of counts, totals and averages by group.