Conditional Aggregates: SUMIF, COUNTIF, AVERAGEIF¶

These functions answer questions like "How much did the North region sell?" — totaling only the rows that match a condition.

A regional sales summary calculated by category

The "IF" Aggregates¶

Function	Answers
`SUMIF`	Total where condition is met
`COUNTIF`	How many meet the condition
`AVERAGEIF`	Average of matching rows

SUMIF Syntax¶

excel

1 line

1=SUMIF(range, criteria, [sum_range])

excel

5 lines

1=SUMIF(A2:A100, "North", C2:C100)
2       │          │        │
3       │          │        └─ cells to add
4       │          └────────── what to match
5       └───────────────────── where to look

Worked Example¶

| Region | Rep   | Sales |
| North  | Ada   | 1200  |
| South  | Grace |  900  |
| North  | Alan  | 1500  |

=SUMIF(A2:A4,"North",C2:C4)     → 2700
=COUNTIF(A2:A4,"North")         → 2
=AVERAGEIF(A2:A4,"North",C2:C4) → 1350

Criteria Can Use Operators & Wildcards¶

excel

4 lines

1=COUNTIF(C2:C100, ">1000")        text operator in quotes
2=SUMIF(B2:B100, "A*", C2:C100)    starts with "A" (* = any chars)
3=COUNTIF(A2:A100, "?at")          "cat","bat","hat" (? = one char)
4=SUMIF(D2:D100, ">="&E1, C2:C100) reference a cell with &

Multiple Criteria: SUMIFS & COUNTIFS¶

When you have more than one condition, use the -IFS versions (note: the sum range comes first):

excel

2 lines

1=SUMIFS(C2:C100, A2:A100,"North", B2:B100,">1000")
2=COUNTIFS(A2:A100,"North", D2:D100,"2026")

Tip

Each criterion in SUMIFS is a pair: criteria_range, criteria. Add as many pairs as you need — they all must be true (AND logic).