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)

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • 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 or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.


Archives Blog Listing

Tag Listing

Learn CF In A Week


Fork me on GitHub