Totals and counts function
AVERAGEIF
Average the matching rows.
Purpose
Average the rows that meet one condition
Returns
The average of the matching rows
Syntax
=AVERAGEIF(range, condition, [average_range])
| Argument | Required | What goes here |
|---|---|---|
range | Required | The column to test. |
condition | Required | What that column has to match. |
average_range | Optional | The column of amounts to average. |
How to use AVERAGEIF
AVERAGEIF is SUMIF divided by COUNTIF, in one formula and with the same argument order: test first, average last.
It only averages the rows that matched. Rows that failed the condition are not counted as nought, they are not there at all.
That is the distinction to be careful about. Average days overdue across only the overdue rows is a different number from average days overdue across the ledger, and both are defensible as long as the label says which one it is.
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 |
|---|---|
=AVERAGEIF(C2:C9,"Overdue",E2:E9)The typical overdue invoice. | 1137 |
=ROUND(AVERAGEIF(D2:D9,">0",D2:D9),1)Average age of the debts that are actually overdue, leaving the current ones out. | 45.8 |
=ROUND(AVERAGE(D2:D9),1)The same column averaged across every row. Lower, because the rows that are not overdue drag it down, and both figures are true. | 34.4 |
Notes
- An AVERAGEIF that matches nothing gives an error rather than nought.
- Say in the label which population was averaged. The two answers differ and neither is wrong.