Excel syntax for SUMPRODUCT(): |
|---|
=SUMPRODUCT(array1[, array2][, array3][, …]) |
The Australian Dollar (AUD) will be used in D8 to illustrate how SUMPRODUCT() can be used to rewrite nested IF()s.
At first, SUMPRODUCT() doesn’t look like a way to rewrite a nested IF(). After all, it uses arrays as inputs! Corresponding items in each array are multiplied to find products for their respective array positions. Once the products are calculated, the products themselves are added to get one final answer: the sum of SUMPRODUCT().
Each array used in SUMPRODUCT() looks similar to the arrays used up to this point (G3:G10, $H$3:$H$10, etc.).
Based on the above description, SUMPRODUCT() is a powerful function. (In the opinion of many Excel users [as well as users of other spreadsheet apps], SUMPRODUCT() is the most powerful spreadsheet function.)
As it is, SUMPRODUCT() will take 2 arrays to calculate the sum of the products:
SUMPRODUCT() item | Cell(s) or range | Value | Comments |
|---|---|---|---|
array1 | $G$3:$G$10 | Codes | 3-letter currency codes |
array2 | $H$3:$H$10 | Rates | Exchange rates to 6 decimal places |
Initial inputs for SUMPRODUCT()
At this point the SUMPRODUCT() we need looks like this:
Normal use of SUMPRODUCT() |
|---|
=SUMPRODUCT($G$3:$G$10, $H$3:$H$10) |
SUMPRODUCT() takes arrays of numbers to calculate a numeric result, yet array1 consists of text values. SUMPRODUCT() can’t work here.
There are 2 details which let us overcome that obstacle:
IF()s use a comparison or test to determine if the comparison is true or false;- Spreadsheet apps like Excel treat TRUE and FALSE as numbers, so TRUE is 1 and FALSE is 0.
This means that array1 can be expressed as a comparison. B8 (AUD) makes its first appearance here:
SUMPRODUCT() item | Cell(s) or range | Value | Comments |
|---|---|---|---|
B8=array1 | B8=$H$3:$H$10 | Tests | Codes converted to TRUE or FALSE values |
array2 | $H$3:$H$10 | Rates | Stays as-is |
Updated SUMPRODUCT() to convert Boolean values to 1’s and 0’s
The revised SUMPRODUCT() looks like this:
Updated SUMPRODUCT() to use TRUE and FALSE values |
|---|
=SUMPRODUCT((B8=$G$3:$G$10), $H$3:$H$10) |
TRUE and FALSE are Boolean values resulting from comparisons or tests, so SUMPRODUCT() needs to do one more thing before it can serve as a nested IF() rewrite.
SUMPRODUCT() item | Cell(s) or range | Value | Comments |
|---|---|---|---|
--(B8=array1) | --(B8=$H$3:$H$10) | 1 or 0 | The double-minus (“--“) without spaces forces Booleans to be 1’s or 0’s |
array2 | $H$3:$H$10 | Rates | Stays as-is |
The final SUMPRODUCT() looks like this:
Updated SUMPRODUCT() to convert Boolean values to 1’s and 0’s |
|---|
=SUMPRODUCT(--(B8=$G$3:$G$10), $H$3:$H$10) |
SUMPRODUCT() took 2 arrays, but one array (in this case, array1) needed 2 modifications before it could be used:
- The array needed to go from an array of text values to an array of Boolean results from comparisons;
- The resulting array of Boolean results needed to be transformed into an array of 1’s and 0’s;
Rates multiplied by 0 end up as 0. Rates multiplied by 1 end up unchanged. Only 1 Rate passed the comparison tests, and that Rate was for AUD (0.771950). Even after seven 0 values are added to the Rate multiplied by 1, the final result is still for AUD.
The above explanation should have shown the power of SUMPRODUCT() and its ability to serve as a rewritten nested IF().
Below is the walkthrough for SUMPRODUCT()
Step 1:
2 arrays to be multiplied before their products are added to get a sum
=SUMPRODUCT(--(B8=$G$3:$G$10), $H$3:$H$10) |
|---|
Step 2:
An array of text values followed by an array of numeric values
=SUMPRODUCT(--{"AUD"; "CAD"; "CHF"; "EUR"; "GBP"; "INR"; "XCD"; "ZAR"}, {0.771950; 0.785590; 1.129640; 1.217170; 1.368300; 0.013700; 0.370194; 0.066029}) |
|---|
Step 3:
B8 is being compared to values in array of text values
=SUMPRODUCT(--{B8="AUD"; B8="CAD"; B8="CHF"; B8="EUR"; B8="GBP"; B8="INR"; B8="XCD"; B8="ZAR"}, {0.771950; 0.785590; 1.129640; 1.217170; 1.368300; 0.013700; 0.370194; 0.066029}) |
|---|
Step 4:
B8=”AUD”, so AUD is being compared to values in array of text values
=SUMPRODUCT(--{"AUD"="AUD"; "AUD"="CAD"; "AUD"="CHF"; "AUD"="EUR"; "AUD"="GBP"; "AUD"="INR"; "AUD"="XCD"; "AUD"="ZAR"}, {0.771950; 0.785590; 1.129640; 1.217170; 1.368300; 0.013700; 0.370194; 0.066029}) |
|---|
Step 5:
Comparisons turned into Boolean results
=SUMPRODUCT(--{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, {0.771950; 0.785590; 1.129640; 1.217170; 1.368300; 0.013700; 0.370194; 0.066029}) |
|---|
Step 6:
“–” is applied to (B8=$G$3:$G$10), turning Boolean results into 1’s and 0’s.
=SUMPRODUCT({1; 0; 0; 0; 0; 0; 0; 0}, {0.771950; 0.785590; 1.129640; 1.217170; 1.368300; 0.013700; 0.370194; 0.066029}) |
|---|
Step 7:
Corresponding array elements are multiplied
=SUMPRODUCT((10.771950); (00.785590); (01.129640); (01.217170); (01.368300); (00.013700); (00.370194); (00.066029)) |
|---|
Step 8:
Products calculated– (0 x anything)=0 and (1 x itself)=itself
=SUMPRODUCT(0.771950; 0; 0; 0; 0; 0; 0; 0) |
|---|
Step 9:
Products (such as they are) are added together to yield the final sum.
0.771950 + 0 + 0 + 0 + 0 + 0 + 0 + 0 |
|---|
This excerpt is taken from 8 Ways To Rewrite Nested IF() Functions at Magna Carta XLS Communications. Each of the 8 nested IF() rewrites from that post will be featured in its own post here.
Other posts will be migrated to this blog before I begin writing posts natively here.