## [Tutorial] The SUMPRODUCT function

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

### [Tutorial] The SUMPRODUCT function

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 B1 Kilos Price2     5     43     2     14     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 A 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 B1 State Sales2    WI   2503    NY  47004    CA  31005    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 C1 State Sales  Year2    WI   250  20163    NY  4700  20174    CA  3100  20175    WI   750  20176    SD   190  20177    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 A1 Value2     33     14    145  1592`

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 first SUMPRODUCT function uses \$ twice and the second uses \$ once.

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 400 the rate is .075
• from 400 to 1000 the rate is .035 of the amount over 400, plus 30 (400×.075)
• after 1000 the rate is .015 of the amount over 1000, plus 51 (400×.075 + 600×.035)
The three lower bounds are 0, 400, and 1000. The increases from each tax rate to the next one are .075 (initial rate), -.04 (.035-.075), and -.02 (.015-.035). The formula is:
=SUMPRODUCT(cell>{0;400;1000};cell-{0;400;1000};{.075;-.04;-.02})

X24: 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.

X25: Count cells which exactly match a string, say "ee". For string "ee", a cell with "EE" is not counted and a cell with "spreadsheet" is not counted.
=SUMPRODUCT(X2:XΩ="ee"))
This formula is not affected by option OpenOffice Calc → Calculate Search criteria = and <> must apply to whole cells. Options are set with OpenOffice → Preferences on a Mac, Tools → Options on other platforms. There is no simple way to make COUNTIF respect the case of the letters.

X26: Count cells which match a string, say "ee", ignoring case. For string "ee", a cell with "EE" is counted and a cell with "spreadsheet" is not counted.
=SUMPRODUCT(UPPER(X2:XΩ)=(UPPER("ee")))
This formula is not affected by option OpenOffice Calc → Calculate Search criteria = and <> must apply to whole cells.

X27: Count cells which contain a string, say "ee". For string "ee", a cell with "spreadsheet" is counted once.
=SUMPRODUCT(X2:XΩ<>SUBSTITUTE(X2:XΩ;"ee";""))
This formula is not affected by option OpenOffice Calc → Calculate Search criteria = and <> must apply to whole cells nor by option Enable regular expressions in formulas.

X28: Count characters in cells, say "e". For character "e", a cell with "spreadsheet" is counted thrice.
=SUMPRODUCT(LEN(X2:XΩ)-LEN(SUBSTITUTE(X2:XΩ;"e";"")))
This formula is not affected by option OpenOffice Calc → Calculate Search criteria = and <> must apply to whole cells nor by option Enable regular expressions in formulas.

X29: Count rows where the values in two columns are the same/different, not possible with COUNTIF/COUNTIFS
=SUMPRODUCT(X2:XΩ=Y2:YΩ)       — Count rows where X value equais the Y value
=SUMPRODUCT(X2:XΩ<>Y2:YΩ)     — Count rows where X value is different than the Y value
=SUMPRODUCT(X2:XΩ>Y2:YΩ)       — Count rows where X value is greater than the Y value

X30: Sum numbers where the values in two columns are the same/different, not possible with SUMIF/SUMIFS
=SUMPRODUCT((S2:SΩ;X2:XΩ=Y2:YΩ)       — Sum column S where X value equais the Y value
=SUMPRODUCT(S2:SΩ;X2:XΩ<>Y2:YΩ)     — Sum column S where X value is different than the Y value
=SUMPRODUCT(S2:SΩ;X2:XΩ>Y2:YΩ)       — Sun column S where X value is greater than the Y value

X31: Count distinct numeric values in X meeting two criteria in Y and Z
=SUMPRODUCT(0<FREQUENCY(IF((Y2:YΩ=α)*(Z2:ZΩ=β);X2:XΩ;"");X2:XΩ))
and press ⇪⌘Enter (Shift+Command+Enter) on a Mac or Ctrl+Shift+Enter on other platforms.
If you press Enter by mistake, use Edit → Delete Contents → Formulas, then try again. To count text values, see X18.

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA). MrProgrammer
Moderator

Posts: 3854
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA