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.

`=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).

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.

```
=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((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((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(B2:B8,C2:C8) / SUM(B2:B8)
```