COUNTIF / COUNTIFS in Excel are powerful functions used for counting with conditions. For example, you can count overdue accounts, sales in a region, or students who scored above 80. These formulas help you analyze data quickly and accurately.
For example:
- How many accounts are overdue?
- How many sales were made in a particular region?
- How many students scored above 80?
This is where COUNTIF and COUNTIFS functions become powerful tools.
What is COUNTIF in Excel?
The COUNTIF function counts the number of cells that meet one condition.
Syntax:
=COUNTIF(range, criteria)
- range → the cells you want to check
- criteria → the condition to count (number, text, expression, or cell reference)
Example:
Count how many accounts are “Overdue”:
=COUNTIF(B2:B10, "Overdue")
What is COUNTIFS in Excel?
The COUNTIFS function counts the number of cells that meet multiple conditions.
Syntax:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example:
Count how many accounts are “Overdue” and belong to the “North” region:
=COUNTIFS(B2:B10, "Overdue", C2:C10, "North")
Practical Examples
We will use this dataset:
| Customer | Region | Status | Amount | Due Date |
|---|---|---|---|---|
| John | North | Overdue | 500 | 01-Jan-25 |
| Mary | South | Paid | 700 | 05-Jan-25 |
| Sam | East | Overdue | 300 | 10-Feb-25 |
| Rita | West | Paid | 400 | 15-Mar-25 |
| Alex | North | Overdue | 600 | 20-Feb-25 |
| Nina | South | Paid | 450 | 25-Mar-25 |
| David | East | Paid | 550 | 05-Feb-25 |
| Sara | North | Overdue | 200 | 10-Jan-25 |
| Leo | South | Paid | 350 | 12-Mar-25 |
| Paul | West | Overdue | 800 | 18-Mar-25 |
COUNTIF / COUNTIFS in Excel – 20 Practice Problems
Basic COUNTIF (Single Condition)
1. Count how many accounts are Overdue
=COUNTIF(C2:C11,"Overdue")
Answer: 5
2. Count how many accounts are Paid
=COUNTIF(C2:C11,"Paid")
Answer: 5
3. Count how many customers are from the North region
=COUNTIF(B2:B11,"North")
Answer: 3
4. Count customers with Amount greater than 500
=COUNTIF(D2:D11,">500")
Answer: 4
5. Count customers with Amount less than 400
=COUNTIF(D2:D11,"<400")
Answer: 3
Intermediate COUNTIFS (Multiple Conditions)
6. Count Overdue accounts in the North region
=COUNTIFS(C2:C11,"Overdue",B2:B11,"North")
Answer: 2 (John, Sara)
7. Count Paid accounts in the South region=COUNTIFS(C2:C11,"Paid",B2:B11,"South")
Answer: 3 (Mary, Nina, Leo)
8. Count Overdue accounts with Amount > 500
=COUNTIFS(C2:C11,"Overdue",D2:D11,">500")
Answer: 2 (Alex, Paul)
9. Count Paid accounts with Amount < 500
=COUNTIFS(C2:C11,"Paid",D2:D11,"<500")
Answer: 3 (Rita, Nina, Leo)
10. Count accounts from East region with Amount > 400
=COUNTIFS(B2:B11,"East",D2:D11,">400")
Answer: 1 (David)
Advanced Conditional Scenarios
11. Count accounts with Due Date in February 2025
=COUNTIFS(E2:E11,">=01-Feb-25",E2:E11,"<=28-Feb-25")
Answer: 2 (Sam, Alex, David → actually 3 total)
12. Count Paid accounts with Due Date in March 2025
=COUNTIFS(C2:C11,"Paid",E2:E11,">=01-Mar-25",E2:E11,"<=31-Mar-25")
Answer: 3 (Rita, Nina, Leo)
13. Count customers with Amount between 400 and 600
=COUNTIFS(D2:D11,">=400",D2:D11,"<=600")
Answer: 4 (John, Rita, Nina, David)
14. Count Overdue accounts not from the South
=COUNTIFS(C2:C11,"Overdue",B2:B11,"<>South")
Answer: 5 (John, Sam, Alex, Sara, Paul)
15. Count Paid accounts from North or South
=COUNTIFS(C2:C11,"Paid",B2:B11,"North")+COUNTIFS(C2:C11,"Paid",B2:B11,"South")
Answer: 3 (Mary, Nina, Leo → South only; North has none Paid)
Text, Dates & Combined Logic
16. Count customers whose name starts with “S”
=COUNTIF(A2:A11,"S*")
Answer: 2 (Sam, Sara)
17. Count customers whose name has exactly 4 letters
=COUNTIF(A2:A11,"????")
Answer: 3 (John, Mary, Sara, Paul → actually 4)
18. Count accounts due before February 2025
=COUNTIF(E2:E11,"<01-Feb-25")
Answer: 2 (John, Sara)
19. Count Paid accounts with Amount ≥ 450 and ≤ 700
=COUNTIFS(C2:C11,"Paid",D2:D11,">=450",D2:D11,"<=700")
Answer: 3 (Mary, Nina, David)
20. Count how many customers are from South region AND Due in March 2025
=COUNTIFS(B2:B11,"South",E2:E11,">=01-Mar-25",E2:E11,"<=31-Mar-25")
Answer: 2 (Nina, Leo)
Final Note
By mastering COUNTIF and COUNTIFS, you can:
- Track overdue payments
- Monitor sales by region
- Analyze trends by date ranges
- Work with both text & numbers easily
Download the free practice file and learn COUNTIF / COUNTIFS in Excel step by step.
