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.
Explore More Excel Functions
Want to master all essential Excel formulas in one place?
Check out our complete guide —
👉 Mastering Excel Formulas: Free Excel Practice Problems for Accounting and Finance
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.
