Math
Adding amounts that match one test
Definition
Adding the numbers in a column for every row whose other column holds a given label, such as red, means giving SUMIF the labels, the word to match, and the numbers.
Syntax
=SUMIF(range, criteria, sum_range)
Arguments
| Argument | Meaning |
|---|---|
range |
The column of labels to scan, for example the color cells. |
criteria |
The label to match. Text needs double quotes, for example "red". |
sum_range |
The column of amounts to add for the matching rows. |
Worked example
A mill lists fabric types and how many rolls were cut. Types are in A2:A5 and rolls are in B2:B5.
| A: Fabric | B: Rolls |
|---|---|
| linen | 22 |
| wool | 8 |
| linen | 15 |
| cotton | 10 |
Formula: =SUMIF(A2:A5,"linen",B2:B5)
Result: 37. linen sits in A2 and A4, so 22 + 15 equals 37.
Practice
Cells C2:C5 hold red, blue, red, and red. Cells D2:D5 hold 7, 3, 8, and 2. In E2, write a formula that totals D2:D5 for every row where C2:C5 equals red.
- In E2, write a SUMIF formula that adds D2:D5 where C2:C5 equals red.
- E2 should show the total of D2:D5 where C2:C5 equals red.
Related functions
SUMIF is the function that totals a number column for rows whose labels match one value.
Common mistake
Testing the number column instead of the label column, as in =SUMIF(D2:D5,"red",C2:C5), so nothing matches red and the total is 0.