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

In our last post, we saw SUMIFS, which is used to ADD Range of Cells using multiple conditions. Similarly, we can use SUMPRODUCT. This function is applicable in Excel versions 1997 and later.

This function is unique because it has multiple uses for example we can use it to ADD ranges of Cells (Array) or to return COUNT of Range of Cells (Arrays) with multiple conditions or to return SUM of Multiplication of 2 adjacent arrays. In addition, we can use multiple criteria from same column in single formula, which is not possible in SUMIFS (We have to use multiple formula to use multiple conditions from same Array)

 SYNTAX: SUMPRODUCT (array1, [array2], [array3], … )

The SUMPRODUCT function syntax has the following arguments:

The SUMPRODUCT function syntax has the following arguments:

  • Array1 (Range of Cells, Mandatory). The first array argument whose components you want to multiply and then add.
  • Array2, array3,   (Range of Cells, [ ] Optional):  Array arguments whose components you want to multiply and then add. (2nd  Array to 255th Array)
Note:
  • All the arrays must have same range size or FUNCTION will return #VALUE! Error( B2:B18, C2:C19 in same function is not allowed)
  • Maximum Arrays can be used is 255.
  • If any cell(s) of Array contains Non-numeric value, SUMPRODUCT returns zero for that particular cell(s).
Operators:
  • Plus (+): It is used when we have multiple criteria from same Array. =SUMPRODUCT(Range1 = “XYZ”+Range1 = “ABC”,Range2) . We have 2 criteria (XYZ and ABC)  from Range1 and Result Range is Range2. it will return sum of all the cells of Range2 where criteria is either ABC or XYZ. Also it converts TRUEs and FALSEs into 1s and 0s.
  • Double Negative(- -): It is used convert TRUEs and FALSEs into 1s and 0s. When we set criteria, function returns TRUE or FALSE depending upon whether criteria is met or not. For example, =SUMPRODUCT(($B$3:$B$18=VALUE(“01/01/2016”)),$G$3:$G$18) will return 0. Because criteria range returns TRUE , in SUMPRODUCT TRUE (Text) by Numeric Value = 0. we use – – in front criteria range =SUMPRODUCT(– –($B$3:$B$18=VALUE(“01/01/2016”)),$G$3:$G$18) which return expected result. Now 1 (Numeric) by Numeric will return expected result.
  • Asterisk (*): It is wildcard as well multiplication operator. if we don’t want to use – – then we can use * instead.=SUMPRODUCT(($B$3:$B$18=VALUE(“01/01/2016”))*$G$3:$G$18). We have used Asterisk in place of comma (which is used to separate criteria).

SUMPRODUCT-Table

SUMPRODUCT-E1

SUMPRODUCT-E2

SUMPRODUCT-E3

To Return COUNT of Cells.

SUMPRODUCT-E4

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

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