Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
COUNTIFS
Three rows have Lagos in the Store column and Earbuds in the Product column: O101, O104 and O108.
Try this lesson free →Example
COUNTIF counts rows that match one condition. COUNTIFS counts rows that match all of them:
=COUNTIFS(C2:C9,"Lagos",D2:D9,"Earbuds")| Order | Day | Store | Product | Qty | Revenue | Report | Answer | |
|---|---|---|---|---|---|---|---|---|
| O101 | Mon | Lagos | Earbuds | 2 | 70 | Lagos Earbuds orders | ||
| O102 | Mon | Abuja | Charger | 1 | 15 | Lagos orders over 50 | ||
| O103 | Tue | Lagos | Phone case | 3 | 24 | Abuja orders over 30 | ||
| O104 | Wed | Lagos | Earbuds | 1 | 35 | Earbuds orders over 50 | ||
| O105 | Wed | Abuja | Earbuds | 4 | 140 | Lagos orders of 3+ | ||
| O106 | Thu | Lagos | Charger | 2 | 30 | |||
| O107 | Fri | Abuja | Phone case | 5 | 40 | |||
| O108 | Fri | Lagos | Earbuds | 3 | 105 |
Key points
C2:C9,"Lagos"A range to check, then what to look for.
D2:D9,"Earbuds"Add another pair for each extra condition. Every pair has to match.
F2:F9,">50"Conditions can be tests too, in quotes.
Unlike SUMIFS, there's no range to add up: =COUNTIFS(C2:C9,"Lagos",F2:F9,">50").
Practice
1. In I2, count the Earbuds orders from Lagos.
=COUNTIFS(C2:C9,"Lagos",D2:D9,"Earbuds")2. In I3, count the Lagos orders worth more than 50.
=COUNTIFS(C2:C9,"Lagos",F2:F9,">50")3. In I4, count the Abuja orders worth more than 30.
=COUNTIFS(C2:C9,"Abuja",F2:F9,">30")4. In I5, count the Earbuds orders worth more than 50.
=COUNTIFS(D2:D9,"Earbuds",F2:F9,">50")5. In I6, count the Lagos orders with a quantity of 3 or more.
=COUNTIFS(C2:C9,"Lagos",E2:E9,">=3")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 →