Practice problems Excel free download for COUNTIF and COUNTIFS functions are perfect for mastering conditional counting in Excel. This guide provides 20 real-world examples with step-by-step solutions, helping you track overdue accounts, monitor sales by region, and analyze data efficiently.
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.
COUNTIF Function in Excel – Practice Problems Excel Free Download
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")
COUNTIFS Function in Excel – Practice Problems Excel Free Download
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.
