|
Getting your Trinity Audio player ready...
|
Learn how to use COUNTIF & COUNTIFS in Excel with real-life examples. This guide explains both functions in simple words to help you count data based on conditions.
What is COUNTIF?
COUNTIF counts how many cells meet a single condition.
Syntax:
=COUNTIF(range, criteria)
range: The group of cells you want to checkcriteria: The rule or condition you want to apply
Example 1: Count How Many Students Passed
| Name | Marks |
|---|---|
| Aditi | 55 |
| Rohan | 42 |
| Tina | 38 |
| Aryan | 71 |
👉 Count how many scored 40 or more (i.e., passed).
=COUNTIF(B2:B5, ">=40")
👉 Result: 3 students passed.
Example 2: Count How Many Apples Sold
| Product | Quantity |
|---|---|
| Apple | 20 |
| Banana | 15 |
| Apple | 30 |
| Mango | 10 |
=COUNTIF(A2:A5, "Apple")
👉 Result: 2 rows have “Apple”.
Example 3: Calculate Average Sales Only If Apple Sold
| Product | Quantity |
|---|---|
| Apple | 20 |
| Banana | 15 |
| Apple | 30 |
| Mango | 10 |
👉 Only calculate average if Apple was sold:
=IF(COUNTIF(A2:A5, "Apple") > 0, AVERAGE(B2:B5), "Apple not sold")
👉 Result: 18.75
What is COUNTIFS?
COUNTIFS lets you count cells only if multiple conditions are true.
Syntax:
=COUNTIFS(range1, criteria1, range2, criteria2, ...)
Note: Each pair checks a new condition.
Example 4: Count Female Employees in HR
| Name | Gender | Department |
|---|---|---|
| Neha | Female | HR |
| Aman | Male | Sales |
| Priya | Female | HR |
| Ravi | Male | HR |
=COUNTIFS(B2:B5, "Female", C2:C5, "HR")
👉 Result: 2
Example 5: Count Orders Above ₹5000 from Delhi
| City | Order Amount |
|---|---|
| Delhi | ₹7000 |
| Mumbai | ₹4000 |
| Delhi | ₹3000 |
| Delhi | ₹8000 |
=COUNTIFS(A2:A5, "Delhi", B2:B5, ">5000")
👉 Result: 2 orders from Delhi above ₹5000
Example 6: Count Attendance in January
| Date | Present |
|---|---|
| 01-Jan-24 | Yes |
| 15-Jan-24 | No |
| 31-Jan-24 | Yes |
| 02-Feb-24 | Yes |
👉 Count how many “Yes” in January only:
=COUNTIFS(A2:A5, ">=01-Jan-2024", A2:A5, "<=31-Jan-2024", B2:B5, "Yes")
👉 Result: 2 present in Jan.
When to Use COUNTIF or COUNTIFS?
| Scenario | Use |
|---|---|
| Count based on 1 condition | COUNTIF |
| Count based on 2 or more rules | COUNTIFS |
Final Thoughts
- Use COUNTIF for quick filtering like “How many Apples?” or “How many passed?”
- Use COUNTIFS when you want extra control, like counting based on 2 or more rules (e.g., region + amount + status).
- These are perfect for reports, dashboards, attendance sheets, and more.
What’s Next?
In the next post, we’ll learn about the AVERAGEIF & AVERAGEIFS in Excel