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.

 

SUMPRODUCT Rules:

  • 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

FORMULA:
=SUMPRODUCT(C2:C8,D2:D8)

 

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).
SUMIF SUMPRODUCT usage

There are two ways to write this expression.
FORMULAS:

=SUMPRODUCT((B$2:B$8=B11)*C$2:C$8)
=SUMPRODUCT(--(B$2:B$8=B11),C$2:C$8)

 

Match Several Criteria, Sum Matches (Like SUMIFS)

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

FORMULAS:

=SUMPRODUCT((B$2:B$8=B11)*(C$2:C$8=C11)*D$2:D$8)
=SUMPRODUCT(--(B$2:B$8=B11),--(C$2:C$8=C11),D$2:D$8)

 

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

FORMULAS:

=SUMPRODUCT((B$2:B$8=B12)*(C$2:C$8=C12)*D$2:D$8*E$2:E$8)
=SUMPRODUCT(--(B$2:B$8=B12),--(C$2:C$8=C12),D$2:D$8,E$2:E$8)

 

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

FORMULAS:

=SUMPRODUCT((B$2:B$8=B12)*(C$2:C$8=C12))
=SUMPRODUCT(--(B$2:B$8=B12),--(C$2:C$8=C12))

 

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

FORMULA:

=SUMPRODUCT(B2:B8,C2:C8) / SUM(B2:B8)

Just want to document some of the Excel functions I end up using fairly frequently, but can be time consuming to reproduce.

Pull Content from Another Sheet by Matching Top Row and Left Column

Basic

=HLOOKUP(TOPROWCELL,'SOURCESHEET'!$1:$100000,MATCH(LEFTREFERENCECELL,'SOURCESHEET'!$A:$A,0),FALSE)

W/ Error Checking

=IFERROR(HLOOKUP(TOPROWCELL,'SOURCESHEET'!$1:$100000,MATCH(LEFTREFERENCECELL,'SOURCESHEET'!$A:$A,0),FALSE),"")

W/ “null” and Error Checking

=IFERROR(IF(HLOOKUP(TOPROWCELL,'SOURCESHEET'!$1:$100000,MATCH(LEFTREFERENCECELL,'SOURCESHEET'!$A:$A,0),FALSE)="null","",HLOOKUP(TOPROWCELL,'SOURCESHEET'!$1:$100000,MATCH(LEFTREFERENCECELL,'SOURCESHEET'!$A:$A,0),FALSE)),"")

Using SUMPRODUCT

=SUMPRODUCT(--('SOURCESHEET'!$A$2:$A$999999=B$1),--('SOURCESHEET'!$B$2:$B$999999=$A2),'SOURCESHEET'!$G$2:$G$999999)

or
=SUMPRODUCT(('SOURCESHEET'!$A$2:$A$999999=B$1)*('SOURCESHEET'!$B$2:$B$999999=$A2)*'SOURCESHEET'!$G$2:$G$999999)
Function requirements:

  • ALL evaluation/comparison ranges need to be exactly the same
  • Evaluation/comparison ranges should have valid, numeric data in all cells