The SUMPRODUCT function

The SUMPRODUCT function is a math and trigonometry function which calculates the sum of a set of multiplications across different ranges of cells. These ranges could be horizontal or vertical as long as the same number of cells exist in each.

This is the format of the SUMPRODUCT function:

=SUMPRODUCT(RANGE 1, RANGE 2, RANGE 3, etc…)

Here is an example of the SUMPRODUCT function:

=SUMPRODUCT({1,3,3,2,6,1},{2,3,1,2,2,4})

= 1x2 + 3x3 + 3x1 + 2x2 + 6x2 + 1x4

=2+9+3+4+12+4 = 34

SUMPRODUCT function returning the sum of the results of the multiplications of each pair of values

This function is valuable when you are making calculations for things like invoices or investments where you are calculating based on multiplication of multiple pieces of information. Especially with the inclusion of different tax rates for necessities versus general goods, versus tobacco and gasoline.

An example of a functional implementation of this would be creating a summary of revenue and taxes collected during a period. In your document you have the unit price of the good, the quantity sold, and the associated tax rate for the product. SUMPRODUCT will let you return your total values in one cell without the need for additional helper columns.

ex: Unit prices are in column B, total quantities are in column D, tax rates are in column G

To calculate revenue:

=SUMPRODUCT(B:B,D:D)

To calculate taxes collected:

=SUMPRODUCT(B:B,D:D,G:G)