Math
SUMIF
Definition
SUMIF returns a total of the numbers whose matching cells pass one test.
Syntax
=SUMIF(range, criteria, sum_range)
Arguments
| Argument | Meaning |
|---|---|
range |
The cells compared with the criterion, such as B2:B5. |
criteria |
The value that counts as a match. Type a number as is; wrap text in double quotes. |
sum_range |
The cells to add when the matching cell in range passes the test. |
Worked example
A print shop logs paper grades and how many sheets each job used. Grades are in B2:B5 and sheet counts are in C2:C5.
| A: Job | B: Grade | C: Sheets |
|---|---|---|
| Poster | 2 | 18 |
| Flyer | 5 | 9 |
| Banner | 2 | 13 |
| Card | 4 | 7 |
Formula: =SUMIF(B2:B5,2,C2:C5)
Result: 31. Grade 2 sits in B2 and B4, so 18 + 13 equals 31.
Practice
Cells A2:A5 hold 4, 9, 4, and 6. Cells B2:B5 hold 10, 20, 15, and 5. In C2, write a formula that totals B2:B5 for rows where A2:A5 equals 4.
- In C2, write a SUMIF formula that adds B2:B5 where A2:A5 equals 4.
- C2 should show the total of B2:B5 where A2:A5 equals 4.
Step 1 of 2
Enter =SUMIF(A2:A5,4,B2:B5) in C2.
A2 and A4 are 4. B2 is 10 and B4 is 15, so 10 + 15 is 25.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses SUMIF.
C2 now shows 25.
Related functions
COUNTIF counts how many cells pass a test, and SUM adds every number with no test. SUMIF keeps one test and returns a total.
Common mistake
Reversing the first and last arguments, as in =SUMIF(C2:C5,2,B2:B5), so the formula tests the sheet counts and adds the grade codes.