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.
Explanation
Excel’s formula engine has some quirks that you should be aware of. One of these quirks is that Excel will treat a text value as larger than a number by default. For example:
=90>100 // returns FALSE
="A">100 // returns TRUE
The second formula above returns TRUE when you probably expect it to return FALSE. You can see this behavior in the worksheet shown in cells D9:D11. We are comparing each value in column B to 100, and the values in these cells return TRUE because they contain text. Essentially, any text value (even a space " “) will be evaluated as greater than any number.
Counting values greater than
This behavior can affect how other formulas count values that are greater than a specific number. For example, the COUNTIF and COUNTIFS functions don’t exhibit this behavior. The formula in G5 returns 1:
=COUNTIF(B5:B11,">100") // returns 1
However a formula that deals with logical expressions directly will show Excel’s native behavior. For example, the SUMPRODUCT formula in cell F7 returns 4:
=SUMPRODUCT(--(B5:B11>100)) // returns 4
This is an example of using Boolean logic in a formula.
Ignoring text values
To ignore text values in a formula like this, you can add an additional check with the ISNUMBER function . The SUMPRODUCT formula below has been modified to check that values are (1) larger than 100 and (2) actually numeric:
=SUMPRODUCT(--(B5:B11>100)*ISNUMBER(B5:B11)) // returns 1
This formula returns 1 as a result.
