🎯
What you'll learn: How to count specific items in a large list with COUNTIF, and how to sum values that match a condition with SUMIF — two of the most-needed functions in business reporting.
COUNTIF — Count Specific Items
COUNTIF counts the number of times a specific item appears in a range — without manually scanning every row.
=COUNTIF(range, criteria)
— range : the column or cells to search through
— criteria : the value to look for (text in quotes, or a number)
— range : the column or cells to search through
— criteria : the value to look for (text in quotes, or a number)
Example — Count by Last Name
| First Name | Last Name | Basic |
|---|---|---|
| Rahul | Verma | 5,000 |
| Rohan | Verma | 5,500 |
| Mohit | Sharma | 5,000 |
| Mohan | Verma | 5,600 |
Imagine this table had 2,000 rows. To count how many employees have the last name "Verma":
=COUNTIF(B2:B5, "Verma")
Result: 3 (Rahul, Rohan, and Mohan Verma)
Result: 3 (Rahul, Rohan, and Mohan Verma)
💡
Use wildcards for partial matches:
=COUNTIF(B2:B5,"V*") counts all last names starting with "V". Use "*"&A1&"*" to count cells containing the value in cell A1.SUMIF — Sum Based on a Condition
SUMIF is similar to COUNTIF but instead of counting, it sums values in one column where a matching condition is met in another column.
=SUMIF(range, criteria, sum_range)
— range : the column to check for the condition
— criteria : the matching value
— sum_range : the column whose values to add up
— range : the column to check for the condition
— criteria : the matching value
— sum_range : the column whose values to add up
Example — Total Salary Where Basic = 5,000
| First Name | Last Name | Basic | Total Salary |
|---|---|---|---|
| Rahul | Verma | 5,000 | 25,000 |
| Rohan | Verma | 5,500 | 30,000 |
| Mohit | Sharma | 5,000 | 26,000 |
| Mohan | Verma | 5,600 | 34,000 |
=SUMIF(C2:C5, 5000, D2:D5)
— C2:C5 : Basic column (what we're checking)
— 5000 : the criteria
— D2:D5 : Salary column (what we're adding up)
Result: 51,000 (Rahul 25,000 + Mohit 26,000)
— C2:C5 : Basic column (what we're checking)
— 5000 : the criteria
— D2:D5 : Salary column (what we're adding up)
Result: 51,000 (Rahul 25,000 + Mohit 26,000)
COUNTIF vs SUMIF — Quick Reference
| Function | Does What | Returns | Use When You Need… |
|---|---|---|---|
COUNTIF | Counts cells matching criteria | A count | How many rows match a condition |
SUMIF | Sums values where criteria match | A total | Total amount for a specific category |
🚀
Going further: Excel also has
COUNTIFS and SUMIFS (plural) which support multiple conditions at once — e.g. sum salary where Department = "Sales" AND Region = "North". These are extremely powerful for dashboards and reports.
📥
Download the example file to practice: countif.zip