Page 1 of 1

[Tutorial] The SUMPRODUCT function

PostPosted: Mon Mar 18, 2019 10:47 pm
by MrProgrammer
Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.

The SUMPRODUCT function has many uses in Calc. This tutorial explores some of those situations. SUMPRODUCT's origin is from mathematics where one often calculates the sum of sets of products. This calculation is also quite common in daily life. For example, if we buy five kilos of apples at $4/kilo, two kilos of bananas at $1/kilo, and three kilos of cherries at $10/kilo, the total cost is (5×4)+(2×1)+(3×10) = 52 dollars. We summed the three sets of products. For the data below, the formula would be =SUMPRODUCT(A2:A4;B2:B4). We've given SUMPRODUCT two ranges of three cells. The first range A2:A4 has the weights purchased. The second range B2:B4 has the prices. SUMPRODUCT multiplies the corresponding cells in the ranges, then adds them to produce a single number. It first performs the three products A2*B2, A3*B3, and A4*B4, then produces the summed result A2*B2 + A3*B3 + A4*B4. SUMPRODUCT always returns a single number,
- Col A Col B
1 Kilos Price
2 5 4
3 2 1
4 3 10

In Calc, an important use of SUMPRODUCT is to perform conditional counting or summation, as one can do with COUNTIF, SUMIF, COUNTIFS, and SUMIFS. SUMPRODUCT can also perform counting and summation involving empty cells. The other four functions ignore empty cells and can't be used in that situation. We can use SUMPRODUCT for counting and summation because the logical functions, some of the information functions, and the relational operators =, <>, <, <=, > and >= produce TRUE or FALSE, where TRUE has the value 1 and FALSE has the value 0. Let's take a look at an example so you can wee why these values are important. We will get total sales for Wisconsin (WI) in the data below using =SUMPRODUCT(B2:B5;A2:A5="WI"). How does that work? Before SUMPRODUCT can perform the multiplications it must evaluate the expression in the second operand, which asks it to compare four cells in column X with the constant "WI". This produces four results: TRUE, FALSE, FALSE, TRUE or equivalently 1, 0, 0, 1. Next Calc performs four multiplications: B2*1, B3*0, B4*0, and B5*1 resulting in 250, 0, 0, and 750. Finally those numbers are added to get 1000. Note how the values in the cells we wanted, B2 and B5, were summed and the others were ignored. Multiplying by 1 (TRUE) causes the value to be included in the sum. Multiplying by 0 (FALSE) causes the value to be omitted from the sum.
- Col A Col B
1 State Sales
2 WI 250
3 NY 4700
4 CA 3100
5 WI 750

In the first two examples, SUMPRODUCT has been given two operands. We can use any reasonable number of operands, as long as the specified ranges have the same size. For example, =SUMPRODUCT(A2:B4;E2:F4;I2:J4;M2:N4) will multiply the corresponding entries the four ranges, then add them. Note that all four operands have three rows and two columns. We multiply four numbers together six (3×2) times, then sum the six products. Specifically, SUMPRODUCT calculates A2*E2*I2*M2 + A3*E3*I3*M3 + A4*E4*I4*M4 + B2*F2*J2*N2 + B3*F3*J3*N3 + B4*F4*J4*N4. You may wonder why anyone would want to use four operands. Building on the last example let's calculate the total of sales which were more than $400 for Wisconsin in 2017 using the data below. Our formula is =SUMPRODUCT(B2:B7;A2:A7="WI";C2:C7=2017;B2:B7>400).
- Col A Col B Col C
1 State Sales Year
2 WI 250 2016
3 NY 4700 2017
4 CA 3100 2017
5 WI 750 2017
6 SD 190 2017
7 WI 440 2017

As before, Calc must evaluate the operands before the multiplications can proceed. It's easier to understand this calculation if we display the values of the four operands in a table, below. Calc multiplies the values in each row, then adds those products. The first, second, third and fifth rows contain a zero so the product is zero. The fourth and sixth rows contain a sales number and three ones so those products are those two sales, 750 and 440. The total of all the products is 0+0+0+750+0+440 or 1190. This is the total for the sales which meet all the criteria.
 250 1 0 0      (product is 0)
4700 0 1 1 (product is 0)
3100 0 1 1 (product is 0)
750 1 1 1 (product is 750)
190 0 1 1 (product is 0)
440 1 1 1 (product is 440)

A special case is when SUMPRODUCT has one operand. In that case there is nothing to multiply; the results are just added. We sometimes use one operand when the goal is to count the numbers of cells which satisfy a single condition. Using the data below, we'll count how many cells contain even numbers. The formula is =SUMPRODUCT(ISEVEN(A2:A5)). ISEVEN is a function which returns 1 (TRUE) for even numbers and 0 (FALSE) for odd numbers. SUMPRODUCT adds 0+0+1+1 for the result 2. Note that you cannot perform this count using COUNTIF without using an auxiliary column to hold the ISEVEN function.
- Col A
1 Value
2 3
3 1
4 14
5 1592

You may wonder, in the last example, why we don't use SUM instead of SUMPRODUCT since no multiplications are being performed. The full answer gets complicated and somewhat beyond the scope of this tutorial. A simplified explanation is: SUMPRODUCT understands what to do with an operand like ISEVEN(A2:A5) but SUM does not. =SUM(ISEVEN(A2:A5)) will give you an answer you don't expect. There are good, but perhaps confusing, reasons for that and, yes, the answer is correct and, yes, there are ways to force SUM to work for this situation. But the important thing to know is that SUMPRODUCT is the correct function to use for conditional counting and summation.

You are encouraged to copy the sample formulas below to a spreadsheet and experiment with SUMPRODUCT, learning by doing. The remainder of the tutorial will list many situations where SUMPRODUCT can be used. You're not expected to study them in detail; they're for reference. Ranges like X2:XΩ or Y2:YΩ denote representative ranges and you will replace them with your actual column name and row numbers, perhaps BB2:BB25. Row 1 is excluded because it is typically a column name. Examples here show data arranged in columns of cells, but SUMPRODUCT also works with data in rows of cells. You'll see "criteria" used several places below. Criteria must be in the form that SUMPRODUCT expects, typically {RangeOfCells}{RelationalOperator}{ComparisonValue}, not the unusual syntax used in COUNTIF(S) and SUMIF(S). For example:
=SUMPRODUCT(Y2:YΩ;X2:XΩ<>0) (syntax for SUMPRODUCT)
=SUMIFS(Y2:YΩ;X2:XΩ;"<>0") (syntax for SUMIFS)

X00: Conditional Counting, with syntax somewhat like COUNTIFS, counting the cells which satisfy all the criteria
=SUMPRODUCT(criteria;criteria;criteria;…)

X01: Conditional Summing, with syntax somewhat like SUMIFS, summing the cells which satisfy all the criteria
=SUMPRODUCT(sumfield;criteria;criteria;criteria;…)

X02: Count negative, zero, non-zero or positive values
=SUMPRODUCT(X2:XΩ<0)
=SUMPRODUCT(X2:XΩ=0)
=SUMPRODUCT(X2:XΩ<>0)
=SUMPRODUCT(X2:XΩ>0)
Note that the comparison value 0 is not enclosed in double quotes.

X03: Count cells from valueA to valueB, inclusive
=SUMPRODUCT(X2:XΩ>=valueA;X2:XΩ<=valueB)

X04: Count cells from valueA to valueB, exclusive
=SUMPRODUCT(X2:XΩ>valueA;X2:XΩ<valueB)

X05: Count cells in range from p to q OR from r to s (disjoint ranges)
=SUMPRODUCT(X2:XΩ>={p;r};X2:XΩ<={q;s})      (not =SUMPRODUCT(X2:XΩ>={p;q};X2:XΩ<={r;s}))

X06: Sum values in column Y where column X is empty
=SUMPRODUCT(Y2:YΩ;ISBLANK(X2:XΩ))
Note that you cannot use SUMIF or SUMIFS for this calculation.

X07: Conditional Averaging, like AVERAGEIFS
=SUMPRODUCT(range;criteria;criteria;…)/SUMPRODUCT(criteria;criteria;…)
The formula above will return a #DIV/0! error if the range does not contain any cells which match the criteria. To avoid that, use two auxiliary cells:
AuxA: =SUMPRODUCT(criteria;criteria;…)
AuxB: =SUMPRODUCT(sumfield;criteria;criteria;…)
=IF(AuxA;AuxB/AuxA;"No matching data")

X08: Average nonzero values
=SUM(X2:XΩ)/SUMPRODUCT(X2:XΩ<>0)

X09: Alternating sum (like =SERIESSUM(-1;0;1;X2:XΩ))
=SUMPRODUCT(X2:XΩ;-1^ISODD(ROW(X2:XΩ)))

X10: Sum all the odd numbered rows
=SUMPRODUCT(X2:XΩ;ISODD(ROW(X2:XΩ)))
Note that you cannot use SUMIF or SUMIFS for this calculation.

X11: Sum every tenth row
=SUMPRODUCT(X2:XΩ;MOD(ROW(X2:XΩ);10)=0)      (change 0 to another number to vary the starting row)

X12: Sum the data (column X) for October, ignoring the year (full date in column Y)
SUMPRODUCT(X2:XΩ;MONTH(Y2:YΩ)=10)
Note that you cannot use SUMIF or SUMIFS for this calculation.

X13: Weighted average, data in column X, weight in column Y
=SUMPRODUCT(X2:XΩ;Y2:YΩ)/SUM(Y2:YΩ)

X14: Same as RANK(X2;$X$2:$X$Ω) but also works with strings
=1+SUMPRODUCT(X2>$X$2:$X$Ω)

X15: Same as RANK(X2;$X$2:$X$Ω) but works with numbers or strings and breaks ties
In row 2, put =SUMPRODUCT(X2>X$2:X$Ω)+SUMPRODUCT(X2=X$2:X2) then fill down.
Note that the second SUMPRODUCT formula uses exactly one $.

X16: Find number in a range X and return corresponding numeric value in another column Y, like DGET
AuxA: =SUMPRODUCT(number=X2:XΩ)
=IF(AuxA=1;SUMPRODUCT(number=X2:XΩ;Y2:YΩ);"Not unique")

X17: Count distinct numeric values (empty cells and text are ignored)
=SUMPRODUCT(FREQUENCY(X2:XΩ;X2:XΩ)>0)

X18: Count distinct text values
Convert the text values to numbers using MATCH(value;range;0) then use the formula above.

X19: Slope of linear trend line (like =SLOPE(Y2:YΩ;X2:XΩ)) if intercept is forced to zero
=SUMPRODUCT(Y2:YΩ;X2:XΩ)/SUMPRODUCT(X2:XΩ;X2:XΩ)

X20: Slope of linear trend line =SLOPE(Y2:Yn;X2:Xn)) if intercept is forced to be K (a specified constant)
=SUMPRODUCT(Y2:YΩ-K;X2:XΩ)/SUMPRODUCT(X2:XΩ;X2:XΩ)

X21: Overtime calculation if time-and-a-half is paid for more than 40 hours
With hours per week in column X and pay rate in column Y:
=SUMPRODUCT(X2:XΩ-{0;40};X2:XΩ>{0;40};Y2:YΩ*{1;0.5})

X22: Overtime calculation if time-and-a-half is paid on Saturdays and double-time is paid on Sundays
With date in column X, hours per day in column Y, and pay rate in column Z:
=SUMPRODUCT(Y2:YΩ;Z2:ZΩ)
+SUMPRODUCT(Y2:YΩ;Z2:ZΩ;TEXT(X2:XΩ;"DDD")="Sat")/2
+SUMPRODUCT(Y2:YΩ;Z2:ZΩ;TEXT(X2:XΩ;"DDD")="Sun")


X23: Value from tax or sales calculation where the rate varies, according to the amount
For example, from 0 to 25 the rate is .0875; from 25 to 1000 the rate is .035; after 1000 the rate is .015). The three lower bounds are 0, 25, and 1000. The increases from each tax rate to the next one are .0875 (initial rate), -.0525 (.035-.0875), and -.02 (.015-.035). The formula is
=SUMPRODUCT(cell-{0;25;1000};cell>{0;25;1000};{.0875;-.0525;-.02})

X24: To count or sum using disjunctive (OR) criteria
If the criteria are disjoint, just add multiple SUMPRODUCT functions. For example, to count the cells in column Z which are 24 or 25 use =SUMPRODUCT(Z2:ZΩ=24)+SUMPRODUCT(Z2:ZΩ=25). Another example, summing the sales (column X) which were not in 2010 (column Y):
=SUMPRODUCT(X2:XΩ;Y2:YΩ<"2010-01-01")+SUMPRODUCT(X2:XΩ;Y2:YΩ>"2010-12-31")
If the criteria aren't disjoint see topic 89228.

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.