Explanation
The goal in this example is to sum a range of Roman numbers. The challenge is that Roman numbers appear as text in Excel, not numeric values. If you try to use the SUM function to sum a range of Roman numbers directly, the result is zero (0).
The solution is to use the ARABIC function to convert the Roman numbers to regular numbers, then sum the result. The ARABIC function takes a valid Roman number and returns its Arabic equivalent. For example:
=ARABIC("V") // returns 5
=ARABIC("IX") // returns 9
=ARABIC("MMXXI") // returns 2021
Notice the Roman numbers are provided as text strings .
In the worksheet shown, we feed the entire range of Roman numbers in B5:B15 into the ARABIC function in one step:
=ARABIC(B5:B15)
Because there are 11 cells in the range, the result is an array that contains 11 numbers:
{1;5;10;25;50;75;100;250;700;1900;2000}
Each number corresponds to a Roman number in B5:B15. This array is returned directly to the SUMPRODUCT function :
=SUMPRODUCT({1;5;10;25;50;75;100;250;700;1900;2000}) // returns 5116
With just one array to process, SUMPRODUCT returns the sum of the numbers: 5116.
This example is a good example of the power of nesting functions together. It also illustrates how array formulas can be quite simple.
Note: the ROMAN function performs the opposite conversion as the ARABIC function, converting Arabic numbers to Roman numbers.
Why SUMPRODUCT and not SUM?
Why can’t we use the SUM function like this:
=SUM(ARABIC(B5:B15))
The answer is a bit complicated. In Excel 365 , you can use SUM without any special consideration*, since Excel 365 handles arrays natively :
=SUM(ARABIC(B5:B15)) // works fine in Excel 365
In other versions of Excel, the SUM function will work, but must be entered as an array formula with Control + Shift + Enter :
{=SUM(ARABIC(B5:B15))} // array form in other versions
With SUMPRODUCT, the formula will work in all versions of Excel:
=SUMPRODUCT(ARABIC(B5:B15)) // works in all versions
In other words, using SUMPRODUCT ensures the formula will work in all versions of Excel ** without any special handling. This is because SUMPRODUCT can handle many array operations natively.
- If an Excel 365 worksheet that uses the SUM option is opened in an earlier version of Excel, Excel will automatically convert the formula to the array form and you will see curly braces {} around the formula. The result will remain unchanged. However, if a user edits the formula, and doesn’t re-enter with Control + Shift + Enter, SUM will not return the correct result.
You will also see curly braces added to the SUMPRODUCT version when a worksheet created with Excel 365 is opened in older versions of Excel. However, in this case, the formula can be re-entered without Control + Shift + Enter and will still return the correct result. In other words, Excel changes the formula to the array form automatically since it contains an array operation, but the array form is not necessary since SUMPRODUCT can handle the array operation natively.
** The ARABIC function was introduced in Excel 2013.
Explanation
The heart of this formula is a basic INDEX and MATCH formula, used to translate text values into numbers as defined in a lookup table. For example, to translate “EX” to the corresponding number, we would use:
=INDEX(value,MATCH("EX",code,0))
which would return 4.
The twist in this problem however is that we want to translate and sum a range of text values in columns C through G to numbers. This means we need to provide more than one lookup value, and we need INDEX to return more than one result. The standard approach is a formula like this:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
After MATCH runs, we have an array with 5 items:
=SUM(INDEX(value,{2,2,3,2,5}))
So it seems INDEX should return 5 results to SUM. However, if you try this, the INDEX function will return only one result SUM. To get INDEX to return multiple results, we need to use a rather obscure trick , and wrap MATCH in N and IF like this:
N(IF(1,MATCH(C5:G5,code,0)))
This effectively forces INDEX to provide more than one value to the SUM function. After INDEX runs, we have:
=SUM({3,3,2,3,-1})
And the SUM function returns the sum of items in the array, 10. For a good write up on this behavior, see this article by Jeff Weir on the StackOverflow site.
