Summing Columns Based On Adjacent Values in Excel

Published: {ts '2013-10-25 00:00:00'}
Author: Steven Neiland
Site Url: http://www.neiland.net/article/summing-columns-based-on-adjacent-values-in-excel/

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)