How To Do Sum/Addition Of Cells with Condition(s) – SUMIF

Most common function we use in excel is SUM function. A simple SUM function adds the range of cells directly. However, what if we want to add only those cells that matches our criteria or condition. There are few functions in Excel, which I am going to explain.

One of the most used function is SUMIF (SUM with one Condition). This function can be used Excel version 2003 and later.

Syntax SUMIF (range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

  • Range(Mandatory). It is your Criteria range (Range of Cells).
  • Criteria(Mandatory). It is the Criteria or Condition, based on which you want Sum.
  • Sum Range(Optional, [ ] are used to show optional arguments). The range of cells for that needs to be added (Result Range). It is optional if your Criteria range is same as Sum Range.
Note: All text (characters) criteria or Mathematical Symbols (Only Exceptions are Numeric) must be in double quotes ("). For e.g.“>”&15,”>15”, 15, C3,”Bob”or “>01/01/2016”.

You can use the wildcard characters.

  • The Question mark (?): A question mark matches any single character and is use to fill in the spaces of characters you want in criteria. For E.g. Bob, Bab, Bib etc. can be used as “B?b” and you will have results of three criteria in one condition for 3 letter word that starts and ends with “b”.
  • Asterisk (*): An asterisk matches any sequence of characters. It is just like using “contains” in Auto Filter. For e.g. “*ob” (Anything that ends with “ob”), “Bo*” (Anything that starts with “Bo”), “*o*” (Anything that has “o” in the middle).
  • Tilde (~): If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character. For e.g. “Bob?” can be written as “?ob~?” (Anything that has “ob” in middle and “?” as last word in 4 letter word.

If we have 2 criteria from same column then use SUMIF 2 times. Please check second last example in below image.

SUMIF-Table

SUMIF-E1

SUMIF-E2

SUMIF-E3

SUMIF-E4

DatesExplanation