Lookups answer "find me this row". Managers rarely ask that. They ask things like: "What did we sell in Mumbai, in March, for product B?" — a total across many rows, but only the rows that qualify.
Back in Module 1 you met COUNTIF, which counted cells passing one test. This lesson gives you the plural versions — SUMIFS and COUNTIFS — which handle as many tests as you like. This pair, plus the lookups you now know, covers most of what an analyst does before lunch.
The data
Rahul's shop has grown again. His sales sheet:
| A | B | C | D |
|---|
| 1 | Date | City | Product | Amount |
| 2 | 03-Mar | Mumbai | Pen | 150 |
| 3 | 05-Mar | Delhi | Bag | 850 |
| 4 | 09-Mar | Mumbai | Bag | 1700 |
| 5 | 11-Apr | Mumbai | Pen | 300 |
...and 500 more rows below.
SUMIFS — the shape
=SUMIFS(D2:D500, B2:B500, "Mumbai")
Read it aloud: "Add up the Amounts, but only where the City is Mumbai."
The order matters and trips people up, so say it once and remember it: the numbers you want to add come FIRST. After that, conditions arrive in pairs — a range, then what that range must equal.