SUMPRODUCT is a very useful and flexible function in Excel, but it also has one of the trickier syntaxes to get right. I use this function often, and concise reference articles have been hard to find.

NOTE: This is for Excel 2010. Other versions may work differently.



  • All arrays/ranges used must be the same length
  • Text and blank cells in the arrays are treated as zeroes
  • Any errors in the arrays will result in an error


Multiply Horizontally, Sum Result

The most basic usage just takes two arrays (ranges) and multiplies each row horizontally, and then adds up the total.
Basic SUMPRODUCT usage



Match Criteria, Sum Matches (Like SUMIF)

This usage matches according to cell reference. The match is translated into a boolean which is then multiplied horizontally (so non-matches are multiplied by zero).

There are two ways to write this expression.



Match Several Criteria, Sum Matches (Like SUMIFS)

Similar to above but you can match multiple criteria like SUMIFS.




Match Several Criteria, Multiply Matching Rows Across

Building on the above uses, instead of just summing matching rows, we can use SUMPRODUCT for a more unique purpose (summing several multiplications at once) that would be hard to emulate with other functions.
SUMPRODUCT - multiple criteria




Count Matching Rows

It’s worth mentioning that we can just stick to boolean evaluations of matching criteria and come up with a simple count of matching rows.
SUMPRODUCT count matching rows




Weighted Averages

SUMPRODUCT also allows us to computes a weighted average all within one cell. Let’s say a Kingdom can vote on a new feature. Each Kingdom must vote as a whole, so one vote per Kingdom. However, if a Kingdom has more members we want their vote to count for more (ie, Electoral College).
SUMPRODUCT weighted average



Leave a reply