Totals and counts function
AVERAGEIFS
Average the rows where every condition holds.
Purpose
Average the rows where every condition holds
Returns
The average of the rows matching all the conditions
Syntax
=AVERAGEIFS(average_range, range1, condition1, [range2], [condition2], ...)
| Argument | Required | What goes here |
|---|---|---|
average_range | Required | The column of amounts to average. |
range1 | Required | The first column to test. |
condition1 | Required | What that column has to match. |
range2 | Optional | The next column to test. |
condition2 | Optional | What that column has to match. |
How to use AVERAGEIFS
AVERAGEIFS puts the column being averaged first and then takes range and condition in pairs, the same shape as SUMIFS.
As with AVERAGEIF, only matching rows are in the average at all.
Narrowing an average twice gets to a very small number of rows very quickly, and an average of two things is not really an average. Count alongside it so the reader knows.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=AVERAGEIFS(E2:E9,C2:C9,"Overdue",D2:D9,">30")The typical invoice among those both overdue and past thirty days. | 1510 |
=COUNTIFS(C2:C9,"Overdue",D2:D9,">30")How many rows that average was taken over. Worth reporting beside it. | 3 |
=AVERAGEIFS(D2:D9,C2:C9,"Overdue")Average age of the overdue rows only. | 45.6 |
Notes
- Every range must be the same size, including the one being averaged.
- Matching no rows gives an error, not nought.