What SUMIF does
SUMIF adds values in one column when a condition in another column is met. Syntax:
=SUMIF(criteria_range, criterion, sum_range)
Where: criteria_range is the column to check, criterion is the condition, and sum_range is the column whose values are added up.
Simple example: total hours by teacher
Column A contains teacher names, column B contains hours. Total hours for Ms Johnson:
=SUMIF(A2:A200, "Johnson", B2:B200)
Or referencing a cell (e.g. E2 contains "Johnson"):
=SUMIF(A2:A200, E2, B2:B200)
SUMIFS for multiple conditions
SUMIFS adds values when all specified conditions are true (AND logic). Syntax:
=SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, ...)
Note: unlike SUMIF, the sum range comes first in SUMIFS.
Example: total hours for Ms Johnson in class 8A:
=SUMIFS(B2:B200, A2:A200, "Johnson", C2:C200, "8A")
Practical use cases in school
- Workload overview: total hours or lessons per teacher, per class or per subject
- Budget tracking: total spend per category or per department
- Score aggregation: total points per student group across multiple tests
- Absence summary: total absent days per student, class or year group
Comparison operators in the criterion
The criterion can use comparison operators (must be in quotes):
=SUMIF(B2:B200, ">=50", C2:C200)– sum values in C where B is 50 or more=SUMIF(B2:B200, "<>0", C2:C200)– sum values where B is not zero
To combine an operator with a cell reference: ">="&E2
SUMIF vs. pivot table
SUMIF is ideal for a fixed summary that updates automatically when the data changes. Pivot tables are more flexible for exploratory analysis. Both have their place – SUMIF is the right choice when the question and the grouping are fixed.