Home › Functions › Math & Trig › =SUMPRODUCT()
Math & Trig All versions ◉ 26 views

=SUMPRODUCT()

How to use the SUMPRODUCT function in Excel, one of Excel’s math and trig functions, using multiplication, addition, subtraction, and/or division to return the sum of the products of corresponding ranges or arrays.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=SUMPRODUCT(array1, [array2], [array3], ...)
Hover argument names in the table for details.
Output typeNumber
Popularity80 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ Download Sample Workbook

A dedicated .xlsx for =SUMPRODUCT() with Persian sample data & live formulas — open in Excel and practice.

  • Sheet “Sample” — data table + formula in place
  • Sheet “Guide” — syntax, arguments & tips from this page
  • Instant & secure (token + rate limit)
📥 Download SUMPRODUCT.xlsx ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

🧠 🧠 Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
array1 Any type Required The first array argument whose components you want to multiply and then add.
array2], [array3],... Range Optional Array arguments 2 to 255 whose components you want to multiply and then add.

▣ Examples

Example =SUMPRODUCT(C2:C5,D2:D5)
Example 2 =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9)

§ Remarks

  • The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. For example, =SUMPRODUCT(C2:C10,D2:D5) will return an error since the ranges aren't the same size.
  • SUMPRODUCT treats non-numeric array entries as if they were zeros.
  • For best performance, SUMPRODUCT should not be used with full column references. Consider =SUMPRODUCT(A:A,B:B), here the function will multiply the 1,048,576 cells in column A by the 1,048,576 cells in column B before adding them.

✦ Tips

  • SUMPRODUCT treats non-numeric array entries as if they were zeros.
  • For best performance, SUMPRODUCT should not be used with full column references. Consider =SUMPRODUCT(A:A,B:B), here the function will multiply the 1,048,576 cells in column A by the 1,048,576 cells in column B before adding them.

⚠ Common Errors

#VALUE! If they do not, SUMPRODUCT returns the #VALUE! error value.
Fix: Make all ranges the same size

⇄ Related Functions

=MMULT() The MMULT function returns the matrix product or multiplication of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2. The way you enter the formula depends on which version of Office 365 you are using. =PRODUCT() The PRODUCT function multiplies all the numbers given as arguments and returns the product. For example, if cells A1 and A2 contain numbers, you can use the formula ⁠=⁠PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2. The PRODUCT function is useful when you need to multiply many cells together. For example, the formula ⁠=⁠PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3. =SUM() How to use the SUM function in Excel to add individual values, cell references, ranges, or a mix of all three. =SUMSQ() Returns the sum of the squares of the arguments. =COUNTIFS() How to use the COUNTIFS function in Excel to apply criteria to cells, across multiple ranges, and count the number of times all criteria are met. =SUMIFS() How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.

💬 Comments & Q&A 0 approved

🔒 Your comment will be published after review.

Share your thoughts — visible after admin approval

No comments yet — be the first!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.