## Summary

To sum a range of Roman numbers, you can use a formula based on the ARABIC and SUMPRODUCT functions. In the example shown, the formula in E5 is:

``````=SUMPRODUCT(ARABIC(B5:B15))
``````

The result is the sum of the Roman numbers in B5:B15. The Arabic numbers in C5:C15 are shown for reference only.

Note: the ARABIC function was introduced in Excel 2013.

## Generic formula

``=SUMPRODUCT(ARABIC(range))``

## 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.

Author

### Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.