Published:
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
A common task when using excel is to sum the values in a column B where the contents of a neighboring column A is a certain value.
Example: Count all apples sold
Take the following excel grid which lists the number of fruit sold on a given date by type. How would we go about getting a count of all the apples sold only.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Fruit | Quantity Sold |
| 2 | 01/01/2013 | Apple | 5 |
| 3 | 02/01/2013 | Orange | 30 |
| 4 | 02/01/2013 | Apple | 6 |
| 5 | 02/01/2013 | Orange | 7 |
| 6 | 03/01/2013 | Apple | 9 |
| 7 | 03/01/2013 | Pear | 30 |
SUMIF to the rescue
This can be accomplished using the SUMIF function which takes three arguments:
SUMIF("cell range to search", "value to search for", "range of cells to sum if found")
To get a sum of all apples sold we search cells B2 -> B7 for the string "Apple" and sum the values in the neighboring cells C2 -> C7 where we find a match.
=SUMIF(B2:B7, "Apple", C2:C7)
Reader Comments