Sum Roman numbers

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.