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. When testing for conditions, 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 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
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;…)
Any text values in the sumfield range are ignored.
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 neither the comparison operator (<,=,<>,>) nor the comparsion value 0 is 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
To avoid a #DIV/0! error if the range does not contain any cells which match the criteria, use two auxiliary cells:
AuxA: =SUMPRODUCT(sumfield;Xcriteria;Ycriteria;…)
AuxB: =SUMPRODUCT(ISNUMBER(sumfield);Xcriteria;Ycriteria;…)
=IF(AuxB;AuxA/AuxB;"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); also see X35
SUMPRODUCT(X2:XΩ;ISNUMBER(Y2:YΩ);MONTH(Y2:YΩ)=10)
Note that you cannot use SUMIF or SUMIFS for this calculation. Also see X35 below.
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 in column X (also see examples X32 through X34)
In an auxillary column, convert the text values to numbers using =SUMPRODUCT(X2>X$2:X$Ω) in the first cell and fill that formula down the column. Then use the formula in example X17 on the auxillary column. All empty cells are counted as one distinct value. All cells with one space are counted as one distinct value. All cells with two spaces are counted as one distinct value, and so on. This formula is similar to the idiom =SUM(1/COUNTIF(X2:XΩ;X2:XΩ)) but does not fail with #DIV/0! if the range contains empty cells. To ignore empty cells, use =IF(LEN(X2);SUMPRODUCT(X2>X$2:X$Ω));"") in the auxillary column and fill down. To ignore empty cells or cells with only spaces, use =IF(LEN(TRIM(X2));SUMPRODUCT(X2>X$2:X$Ω));"") in the auxillary column and fill down.
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 text 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(EXACT(X2:XΩ;"ee")))
This formula is not affected by option OpenOffice Calc → Calculate → Case Sensitive or by option 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 text 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")) or =SUMPRODUCT(UPPER(X2:XΩ)="EE")
This formula is not affected by option OpenOffice Calc → Calculate → Case Sensitive or by option Search criteria = and <> must apply to whole cells.
X27: Count text 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Ω) — Sum 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 (if no criteria, see X17)
=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.
X32: Count distinct text values in X
=SUMPRODUCT(1/MMULT(X2:XΩ=TRANSPOSE(X2:XΩ);SIGN(ROW(X2:XΩ))))
All empty cells are counted as one distinct value. All cells with one space are counted as one distinct value. All cells with two spaces are counted as one distinct value, and so on. This formula is similar to the idiom =SUM(1/COUNTIF(X2:XΩ;X2:XΩ)) but does not fail with #DIV/0! if the range contains empty cells. The COUNTIF formula requires option Calc → Calculate → Search criteria = and <> must apply to whole cells and option Enable regular expressions in formulas should probably be disabled. The SUMPRODUCT formula works with any combination of options. Options are set with OpenOffice → Preferences on a Mac, Tools → Options on other platforms. This formula may not be suitable for a large range (depends on your computer), but you can use example X18.
X33: Count distinct text values in X but ignore empty cells
=SUMPRODUCT(1/MMULT(X2:XΩ=TRANSPOSE(X2:XΩ);1E111*(X2:XΩ="")+1))
All cells with one space are counted as one distinct value. All cells with two spaces are counted as one distinct value, and so on. This formula may not be suitable for a large range (depends on your computer), but you can use example X18.
X34: Count distinct text values in X but ignore empty cells and cells with only spaces
=SUMPRODUCT(1/MMULT(X2:XΩ=TRANSPOSE(X2:XΩ);1E111*(LEN(TRIM(X2:XΩ))=0)+1))
Cells with non-breaking spaces or other invisible characters are not ignored. This formula may not be suitable for a large range (depends on your computer), but you can use example X18.
X35: Sum the data (column X) for a specific month (column Y), say October 2021; also see X12
=SUMPRODUCT(X2:XΩ;TEXT(Y2:YΩ;"YYYYMM")="202110") or
=SUMPRODUCT(X2:XΩ;YEAR(Y2:YΩ)=2021;MONTH(Y2:YΩ)=10)
X36: Caution for = or <> operators in text value comparisons
The comparisons SUMPRODUCT(…;«values»=«values»;…) or SUMPRODUCT(…;«values»<>«values»;…) are affected by option OpenOffice Calc → Calculate → Case Sensitive. Options are set with OpenOffice → Preferences on a Mac, Tools → Options on other platforms. See X25 and X26 for alternate comparisons which do not depend on the setting of the option.
X37: Bitwise operations for 48-bit unsigned numbers
First use Insert → Names → Define → Name → Two047 → AssignedTo → 2^(ROW($A$1:$A$48)-ROW($A$1)) → OK
BITAND(X2;X3) is =SUMPRODUCT(Two047;ISODD(QUOTIENT(X2;Two047));ISODD(QUOTIENT(X3;Two047)))
BITOR(X2;X3) is =SUM(Two047)-SUMPRODUCT(Two047;ISEVEN(QUOTIENT(X2;Two047));ISEVEN(QUOTIENT(X3;Two047)))
BITXOR(X2;X3) is =SUMPRODUCT(Two047;ISODD(QUOTIENT(X2;Two047))<>ISODD(QUOTIENT(X3;Two047)))
BITNOT(X2) is =SUM(Two047)-X2
Enter these as array formulas.
Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.