Summing Columns Based On Adjacent Values in Excel

Author: Steven Neiland

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

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing