A common workplace request is: βCan you calculate the totals by criteria?β The functions that help with this are SUMIF and COUNTIF. Simply specify the criteria, and the totals and counts are calculated automatically.
β SUMIF Function β Total Based on a Criterion
Syntax:
=SUMIF(criteria range, criteria, [sum range])
Example: Find the total sales for the βSeoulβ region
=SUMIF(A2:A20, "Seoul", B2:B20)
β COUNTIF Function β Count Based on a Criterion
Syntax:
=COUNTIF(criteria range, criteria)
Example: Count tasks with a βCompletedβ status
=COUNTIF(C2:C50, "Completed")
β Practical Example 1: Count Employees by Department
=COUNTIF(B2:B100, "Sales Department")
Automatically calculates the number of employees in the βSales Department.β
β Practical Example 2: Total Scores for Students Who Scored 100 or Higher
=SUMIF(D2:D50, ">=100")
Adds the test scores of students who scored 100 or higher.
β Practical Example 3: Total Sales for a Specific Period
Find the total sales for January 2025 only.
=SUMIF(A2:A100, "2025-01*", B2:B100)
=COUNTIF(A2:A50, "Kim*") β Number of people whose names start with Kim
π Summary
- SUMIF: Calculates totals by criteria
- COUNTIF: Counts items by criteria
- Practical uses: Managing sales, employees, and work performance
π Frequently Asked Questions (FAQ)
Q1. What is the difference between SUMIF and COUNTIF?
A. SUMIF calculates totals, while COUNTIF calculates counts.
Q2. Can I apply multiple criteria at the same time?
A. Yes, you can use SUMIFS and COUNTIFS.
Q3. Can I apply date criteria?
A. Yes. For example: =COUNTIF(A2:A50, ">2025-01-01")
Q4. Is case sensitivity required?
A. COUNTIF and SUMIF are not case-sensitive.
Once you learn SUMIF and COUNTIF, creating reports by criteria becomes much easier. Put them to work today. π