Concept
1. Practice sheet
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Salesperson | Region | Product | Amount |
| 2 | 01-Apr | Amit | North | Laptop | 55000 |
| 3 | 03-Apr | Neha | South | Mobile | 18000 |
| 4 | 05-Apr | Amit | North | Mobile | 22000 |
| 5 | 08-Apr | Ravi | West | Laptop | 61000 |
| 6 | 12-Apr | Neha | South | Laptop | 48000 |
| 7 | 15-Apr | Amit | South | Tablet | 30000 |
| 8 | 20-Apr | Ravi | North | Mobile | 15000 |
2. SUMIFS — total with conditions
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
First, what to add up; then pairs of "where to look, what to find".
Amit's total sales:
=SUMIFS(E2:E8, B2:B8, "Amit") → 107000
Amit's North region sales (2 conditions):
=SUMIFS(E2:E8, B2:B8, "Amit", C2:C8, "North") → 77000
Multiple conditions work like AND — all must match.
3. COUNTIFS — count with conditions
There's no sum_range here, just the pairs:
=COUNTIFS(D2:D8, "Laptop") → 3
=COUNTIFS(D2:D8, "Laptop", E2:E8, ">50000") → 2
4. AVERAGEIFS — average with conditions
Same structure as SUMIFS:
=AVERAGEIFS(E2:E8, D2:D8, "Mobile") → 18333.33
If no row matches, you get #DIV/0! (we'll handle that in Lesson 7).
5. Ways to write criteria
| You want | Criteria | Note |
|---|---|---|
| Exact text | "Amit" |
case-insensitive |
| Greater than a number | ">50000" |
operator inside quotes |
| Not equal to | "<>North" |
|
| Starts with "Lap" | "Lap*" |
* = any number of characters |
| Blank cells | "" |
|
| Value from a cell | H1 |
no quotes |
| Cell + operator | ">"&H1 |
join with & |
The last one is the most important. If you write ">H1", Excel will literally look for the text "H1". The correct way is ">"&H1.
6. Date range — sales in a period
With 01-Apr in H1 and 10-Apr in H2:
=SUMIFS(E2:E8, A2:A8, ">="&H1, A2:A8, "<="&H2) → 156000
Two conditions on the same column (A) — completely allowed.
7. Build a summary report (with $)
Type North, South, West in G2:G4. In H2:
=SUMIFS($E$2:$E$8, $C$2:$C$8, G2)
Drag down. The ranges are locked; only G2 changes. The $ from Lesson 1 pays off here.
8. SUMIF vs SUMIFS
The older SUMIF (without S) has a different argument order — sum_range comes last. To avoid confusion, always use SUMIFS, even for a single condition.
Common mistakes
Ranges of different sizes (E2:E8 and B2:B10) — #VALUE! error. Writing the operator outside the quotes, or the cell reference inside the quotes. Text-numbers in the Amount column, which don't get added to the total.