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.
The solution is to use theARABIC functionto 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:
Notice the Roman numbers are provided astext strings.
This example is a good example of the power ofnestingfunctions together.
It also illustrates howarray formulascan be quite simple.
Note: theROMAN functionperforms 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:
The answer is a bit complicated.
This is because SUMPRODUCT can handle many array operations natively.
The result will remain unchanged.
** TheARABIC functionwas introduced in Excel 2013.
For example, the formula =ARABIC(“VII”) returns 7.
ROMAN Function
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
