Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
SUMIFS
It adds the revenue only on rows where the store is Lagos and the product is Earbuds: 70 + 35 + 105 = 210.
Try this lesson free →Example
SUMIF checks one condition. SUMIFS checks as many as you like:
=SUMIFS(F2:F9,C2:C9,"Lagos",D2:D9,"Earbuds")| Order | Day | Store | Product | Qty | Revenue | Report | Answer | |
|---|---|---|---|---|---|---|---|---|
| O101 | Mon | Lagos | Earbuds | 2 | 70 | Lagos Earbuds revenue | ||
| O102 | Mon | Abuja | Charger | 1 | 15 | Lagos revenue over 50 | ||
| O103 | Tue | Lagos | Phone case | 3 | 24 | Abuja Earbuds revenue | ||
| O104 | Wed | Lagos | Earbuds | 1 | 35 | Lagos chargers sold | ||
| O105 | Wed | Abuja | Earbuds | 4 | 140 | Abuja revenue over 20 | ||
| O106 | Thu | Lagos | Charger | 2 | 30 | |||
| O107 | Fri | Abuja | Phone case | 5 | 40 | |||
| O108 | Fri | Lagos | Earbuds | 3 | 105 |
Key points
F2:F9First: the range to add up. SUMIF puts it last, SUMIFS puts it first.
C2:C9,"Lagos"Then pairs: a range to check, and what to look for.
D2:D9,"Earbuds"Add as many pairs as you need. Every one has to match.
Put together: =SUMIFS(F2:F9,C2:C9,"Lagos",D2:D9,"Earbuds"). A condition can be a test too, like ">50".
Practice
1. In I2, total the revenue from Lagos Earbuds orders.
=SUMIFS(F2:F9,C2:C9,"Lagos",D2:D9,"Earbuds")2. In I3, total the revenue from Lagos orders worth more than 50. The second pair checks the revenue itself: F2:F9 with ">50".
=SUMIFS(F2:F9,C2:C9,"Lagos",F2:F9,">50")3. In I4, total the revenue from Abuja Earbuds orders.
=SUMIFS(F2:F9,C2:C9,"Abuja",D2:D9,"Earbuds")4. In I5, total how many chargers Lagos sold. Add up the Qty column this time.
=SUMIFS(E2:E9,C2:C9,"Lagos",D2:D9,"Charger")5. In I6, total the revenue from Abuja orders worth more than 20.
=SUMIFS(F2:F9,C2:C9,"Abuja",F2:F9,">20")Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →