Google Sheets Array To Sum Text

0
votes

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.

2
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

2 Answers

1
votes

use:

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

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

enter image description here

If that is not possible then use IFERROR:

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

enter image description here

0
votes

try:

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

0

shorter:

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