IntermediateTotals and maths
Syntax
=AVERAGEIF(range, criteria, [average_range])| Argument | What it means |
|---|---|
range | The cells to test. |
criteria | The condition. |
average_range | The numbers to average. If left out, the tested range is averaged. |
Examples with results
Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.
Team list (fictional)
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Name | Department | Salary | Start date | Rating |
| 2 | Amira Hassan | Finance | 9200 | 2019-03-04 | 4 |
| 3 | Ben Carter | Sales | 7800 | 2021-07-19 | 3 |
| 4 | Chen Wei | Sales | 8100 | 2020-01-13 | 5 |
| 5 | Dina Ali | HR | 7200 | 2022-09-01 | 4 |
| 6 | Emil Novak | Operations | 6900 | 2018-11-26 | 2 |
| 7 | Farah Khan | Finance | 10400 | 2016-05-09 | 5 |
| 8 | Gabriel Silva | Operations | 6500 | 2023-02-14 | 3 |
=AVERAGEIF(B2:B8,"Sales",C2:C8)Average salary in Sales
Office supplies sales (fictional)
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Item | Qty | Unit price | Region | Date |
| 2 | Pen | 10 | 1.5 | North | 2025-01-15 |
| 3 | Notebook | 5 | 12 | South | 2025-02-03 |
| 4 | Pen | 20 | 1.5 | South | 2025-02-28 |
| 5 | Stapler | 2 | 40 | North | 2025-03-10 |
| 6 | Folder | 15 | 3.25 | East | 2025-03-22 |
| 7 | Notebook | 8 | 12 | East | 2025-04-05 |
| 8 | Pen | 12 | 1.5 | North | 2025-04-18 |
| 9 | Marker | 30 | 2 | South | 2025-05-02 |
=AVERAGEIF(A2:A9,"Pen",B2:B9)Average Pen order size
Try it yourself
Change the formula or switch to other sample data.
Press Calculate to see the result.
The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.
How people use AVERAGEIF at work
- HR
Average rating in Operations.
Data: Team list (fictional)=AVERAGEIF(B2:B8,"Operations",E2:E8) - Sales
Average order size in the North.
Data: Office supplies sales (fictional)=AVERAGEIF(D2:D9,"North",B2:B9)
Common mistakes and tips
- If no row matches you get #DIV/0!. Wrap the formula in IFERROR to show a friendly message.
Works in
All versions of Excel, and Google Sheets.
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.