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

In our last post we saw SUMIF in which we can ADD range of cells based in 1 Condition, however what if we had multiple conditions. For e.g. we have to ADD Salary of “Adam” for month of January Only.

We can use SUMIFS, as the suggest we can add the range of cells that’s meet our multiple criteria’

Syntax:  SUMIFS (sum range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

DIFFERENCE BETWEEN SUMIF AND SUMIFS

SUMIF SUMIFS
One Condition/Criteria Multiple Criteria (Maximum 127 Criteria)
Sum Range is last Argument Sum Range is first Argument
Sum Range is Optional if Sum Range and Criteria Range is same Sum Range is Mandatory though both range are same.

 The SUMIFS function syntax has the following arguments:

Sum Range (Mandatory). The range of cells for that needs to be added.

Criteria Range 1(Mandatory). It is the Criteria or Condition, based on which you want Sum.

Criteria 1 (Mandatory). It is your Criteria range (Range of Cells).

Criteria Range 2, Criteria 2…. (Optional). Additional criteria ranges and their associated criteria. You can enter up to 127 range/criteria pairs.

Note: If multiple criteria is from same column then we have use SUMIFS multiple times. '=SUMIFS (E2:E18, C2:C18,"Accounts", B2:B18,"01/01/2016")+
SUMIFS (E2:E18, C2:C18,"Accounts", B2:B18,"03/01/2016)"
(Check third example in below pic)

Wild Characters (You can read Wild Cards in detail in our last post On SUMIF)

  • Question Mark (?): Matches single character (Bob, Bab becomes B?b)
  • Asterisk (*): Matches multiple characters (Adam, Annam becomes A*m)
  • Tilde (~): When we have use actual question mark or asterisk as a criteria (Adam? Annam? Becomes A*m~?)Table
    SUMIFS-E1

SUMIFS-E2

SUMIFS-E3

DatesExplanation