SUMPRODUCT() function in Excel

SUMPRODUCT function: Syntax

Get FREE Advanced Excel Exercises with Solutions!

 SUMPRODUCT (array1, [array2], [array3], …)

SUMPRODUCT function: Example

Example: =SUMPRODUCT(A2:B4, C2:D4). This example says that components (A2, A3, A4, B2, B3, and B4) of Array1 (A2:B4) will be multiplied with the components (C2, C3, C4, D2, D3, and D4) of Array2 (C2:D4) and then summed. So the calculation will be: A2*C2+ A3*C3+ A4*C4+ B2*D2+ B3*D3+ B4*D4.

The following figure shows some real-time examples.

SUMPRODUCT() function in Excel

Some product real-time examples.

Read More: 44 Mathematical Functions in Excel (Download Free PDF)

SUMPRODUCT function: Caution

  • The array arguments must have the same dimensions. If they do not have the same dimensions, SUMPRODUCT function will return the #VALUE! error.
  • SUMPRODUCT treats not numeric array values as zeros.

Download Working File

Happy Excelling 🙂

Further Readings

Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy:

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF