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:
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:
After MATCH runs, we have an array with 5 items:
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:
This effectively forces INDEX to provide more than one value to the SUM function. After INDEX runs, we have:
And the SUM function returns the sum of items in the array, 10. For a good write up on this behavior, see this interesting article on the EXCELXOR website.