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

Leave a Reply

Your email address will not be published. Required fields are marked *