SUMPRODUCT()

SUMPRODUCT() - Multiplies corresponding elements in the given arrays, and returns the sum of those products.

Syntax

SUMPRODUCT(Array 1; Array 2...Array 30)

Array 1, Array 2...Array 30 represent arrays whose corresponding elements are to be multiplied.

At least one array must be part of the argument list. If only one array is given, all array elements are summed.

I really like this SUMPRODUCT() function. Below is an example spreadsheet. The spreadsheet contains formulas in the Turquoise, Yellow, and Green cells. The formulas for each color are similar. So, I will explain one formula from each color group.

Turquoise Colored Cells
In cell D14 I want to find the number of Ford's sold during the month of June.
Cell D14 contains the formula =SUMPRODUCT((A4:A12="Ford")*(B4:B12="June")*(C4:C12))

The first part of the formula looks through cells A4:A12 and returns a value of 1 (True) or (0) False. The second part of the formula looks through cells B4:B12 and returns a value of 1 (True) or 0 (False). It then multiplies the these results and the values from column C and then sums the products. See the screen shot below for a better representation of how the formula is calculating the results.



Yellow Colored Cells
In cell D18 I want to know how much money I made during the month of June on Ford's
Cell D18 contains =SUMPRODUCT((A4:A12="Ford")*(B4:B12="June")*(C4:C12)*(D4:D12))

The first part of the formula looks through cells A4:A12 and returns a value of 1 (True) or (0) False. The second part of the formula looks through cells B4:B12 and returns a value of 1 (True) or 0 (False). It then multiplies the these results and the values from columns C and D and then sums the products. The screen shot for how this is calculated would be similar to the above table.

Green Colored Cells
In cell D22 I want to know how many cars I sold in June.
Cell D22 contains the formula =SUMPRODUCT((B4:B12="June")*(C4:C12))

The first part of the formula looks through cells B4:B12 and returns a value of 1 (True) or 0 (False). It then multiplies the these results and the values from column C and then sums the products. The screen shot for how this is calculated would be similar to the above table.


Here is a screen shot of my spreadsheet. You can click on "Spreadsheet Function Files" under "Links" on the right hand side of the page.