Google Sheets Array To Sum Text

Question

I've found the following example that works in Excel for my use case, which is to convert a range of text values to numbers and sum the returned array.

=SUM(INDEX(Values,N(IF(1,MATCH($B$4:$B$6,Map,0)))))

In the above formula, Values is a range [4,3,2,1,0] corresponding to a word Map [Excellent,Good, Fair, Poor, ].

If my range B4:B6 is [Fair,,Fair] I would expect to have a value of 4 returned.

This solution works in Excel, but I cannot seem to get this same function to work in Google Sheets by adding arrayformula around the working excel formula.

Any help here would be greatly appreciated.

Answer 1

try:

=SUM(ARRAYFORMULA(IFNA(VLOOKUP(A:A, D:E, 2, 0))))

---

shorter:

=SUM(INDEX(IFNA(VLOOKUP(A:A, D:E, 2, 0))))

Answer 2

use:

=ArrayFormula(sum(VLOOKUP(B4:B6,{Map,Values},2,false)))

But your blank must be an empty string in both places: =""

---

If that is not possible then use IFERROR:

=ArrayFormula(sum(iferror(VLOOKUP(B4:B6,{Map,Values},2,false),0)))