SUMPRODUCT function

SUMPRODUCT(FirstValue, Values...) SUMPRODUCT(FirstValue; Values...)

FirstValue

{ Number } or { Logical }

The first value.

Values

{ Number } or { Logical } (accepts many)

The remaining values.

Returns

Number

The sum of the given arrays' products.

Multiplies all the given arrays together and returns the sum of products. SUMPRODUCT({ FALSE, TRUE, TRUE }, { 10, 20, 30 })SUMPRODUCT({ FALSE; TRUE; TRUE }; { 10; 20; 30 }) returns 50 (0 * 10 + 1 * 20 + 1 * 300 * 10 + 1 * 20 + 1 * 30).

SUMPRODUCT has many uses. Refer to the section below for a detailed example.

TRUE values are interpreted as 1 and FALSE values are interpreted as 0. The formula above is equivalent to SUMPRODUCT({ 0, 1, 1 }, { 10, 20, 30 })SUMPRODUCT({ 0; 1; 1 }; { 10; 20; 30 }). As such, there is no need to use the -- operator to turn logical arrays into number arrays for use with SUMPRODUCT.

Calculating salaries with SUMPRODUCT

Consider two arrays representing table columns. One holds the salaries of the employees of an organization, and the other holds their positions. This is the array with salaries:

{ 2500, 2200, 4000, 3000 }{ 2500; 2200; 4000; 3000 }

This is the array with positions:

{ "Junior associate", "Junior associate", "Senior
executive", "Executive" }
{ "Junior associate"; "Junior associate"; "Senior
executive"; "Executive" }

To only return salaries associated with junior associates, the following formula may be used:

SUMPRODUCT({ 2500, 2200, 4000, 3000 }, { "Junior
associate", "Junior associate", "Senior executive", "Executive" } = "Junior
associate")
SUMPRODUCT({ 2500; 2200; 4000; 3000 }; { "Junior
associate"; "Junior associate"; "Senior executive"; "Executive" } = "Junior
associate")

The formula fragment { "Junior associate", "Junior
associate", "Senior executive", "Executive" } = "Junior associate"
{ "Junior associate"; "Junior
associate"; "Senior executive"; "Executive" } = "Junior associate"
is evaluated to the logical array { TRUE, TRUE, FALSE, FALSE }{ TRUE; TRUE; FALSE; FALSE }, which SUMPRODUCT interprets as the number array { 1, 1, 0, 0 }{ 1; 1; 0; 0 }.

The final SUMPRODUCT invocation can, as a result, be read as follows:

SUMPRODUCT({ 2500, 2200, 4000, 3000 }, { 1, 1, 0, 0 })SUMPRODUCT({ 2500; 2200; 4000; 3000 }; { 1; 1; 0; 0 })

The formula above returns 4700 (2500 * 1 + 2200 * 1 + 4000 * 0 + 3000 * 02500 * 1 + 2200 * 1 + 4000 * 0 + 3000 * 0), which is the total salary cost for the two junior associates on staff.

Examples

SUMPRODUCT({ 0, 1, 1 }, { 10, 20, 30 })SUMPRODUCT({ 0; 1; 1 }; { 10; 20; 30 })

Returns 50 (0 * 10 + 1 * 20 + 1 * 300 * 10 + 1 * 20 + 1 * 30).

SUMPRODUCT({ FALSE, TRUE, TRUE }, { 10, 20, 30 })SUMPRODUCT({ FALSE; TRUE; TRUE }; { 10; 20; 30 })

Returns 50 (0 * 10 + 1 * 20 + 1 * 300 * 10 + 1 * 20 + 1 * 30).

SUMPRODUCT({ 1, 2, 3 }, { 10, 20, 30 } > 15)SUMPRODUCT({ 1; 2; 3 }; { 10; 20; 30 } > 15)

{ 10, 20, 30 } > 15{ 10; 20; 30 } > 15 is equivalent to { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE } (because only the last two elements of the array are greater than 15). SUMPRODUCT interprets { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE } as { 0, 1, 1 }{ 0; 1; 1 }, meaning that this example formula is equivalent to SUMPRODUCT({ 1, 2, 3 }, { 0, 1, 1 })SUMPRODUCT({ 1; 2; 3 }; { 0; 1; 1 }). It returns 5 (1 * 0 + 2 * 1 + 3 * 11 * 0 + 2 * 1 + 3 * 1). Spreadsheet-savvy users may be tempted to write this formula as SUMPRODUCT({ 1, 2, 3 }, --({ 10, 20, 30 } > 15))SUMPRODUCT({ 1; 2; 3 }; --({ 10; 20; 30 } > 15)), but while that formula works with Calcapp, the -- operator is not necessary.