📊 Excel Basics #14 – COUNTIF() & COUNTIFS() Functions

The "COUNTIF()" and "COUNTIFS()" functions count cells that meet one or more conditions. They are extremely useful for analyzing large datasets and creating reports.

📌 1. COUNTIF() Function
The "COUNTIF()" function counts cells based on a single condition.

Syntax:
=COUNTIF(range, criteria)

Example:
A
Apple
Banana
Apple
Orange
Apple

Formula:
=COUNTIF(A2:A6,"Apple")

Result:
3

Excel counts how many times "Apple" appears.

📌 Example with Numbers
Sales
5000
12000
8000
15000
20000

Formula:
=COUNTIF(A2:A6,">10000")

Result:
3

This counts all sales greater than 10,000.

📌 2. COUNTIFS() Function
The "COUNTIFS()" function counts cells based on multiple conditions.

Syntax:
=COUNTIFS(criteriaᵣange1, criteria1, criteriaᵣange2, criteria2,...)

Example:
Employee | Department | Sales
Rahul | IT | 60000
Priya | HR | 55000
Amit | IT | 45000
Neha | IT | 70000

Formula:
=COUNTIFS(B2:B5,"IT",C2:C5,">50000")

Result:
2

This counts employees who:
• Belong to the IT department.
• Have sales greater than 50,000.

📌 Common Criteria Examples
• "Apple" → Exact text
• ">100" → Greater than
• "<50" → Less than
• ">=1000" → Greater than or equal to
• "<>0" → Not equal to zero

📌 Real-World Uses
• Count employees in a department.
• Count products with sales above a target.
• Count overdue invoices.
• Count students who scored above a passing mark.
• Count orders from a specific region.

📌 Common Mistakes
• Selecting ranges of different sizes in "COUNTIFS()".
• Forgetting to enclose text criteria in double quotes.
• Using incorrect comparison operators.

✅ Best Practices
• Use "COUNTIF()" for a single condition.
• Use "COUNTIFS()" when multiple conditions are required.
• Ensure all criteria ranges in "COUNTIFS()" are the same size.
• Use cell references for criteria to make formulas dynamic.

Example:
=COUNTIF(B2:B100,E1)

If E1 contains "IT", Excel counts all IT records automatically.

Double Tap ❤️ For More