Google Sheets right() in Sum in Arrayformula returning false values

0
votes

I've a column of sample data where each cell is either blank or (3 alpha chars, 1 white space, 1 digit). For example:

enter image description here

I need to check if the cell begins with "GTR" or "DBT", then return the number, and sum the return of the column. I'm using the formula below:

=ARRAYFORMULA(sum(IF(OR(left(A1:A10,3)="GTR",left(A1:A10,3)="DBT"),VALUE(right(A1:A10,1)),0)))

The problem is that instead of returning 20, it returns 52.

In fact it appears to return the last char of any cell in the range. (eg. if "A5" has a value of 'someText' the formula returns an error because value() can't parse 't' into a number.

I'd like to know if anyone can tell me how to solve this problem, or if there's something wrong with my formula?

Here's an example of this problem in a Google Sheet: https://docs.google.com/spreadsheets/d/1XNVUWhI43UW2ABrja8rmplmxhhkSu-je45F-9F_3GQM/edit#gid=0

Thanks, Onji

2
google-sheetsarray-formulas

2 Answers

0
votes

The ArrayFormula plus OR will evaluate to TRUE if any of the cells in the range is in the codition, to overcome this remove the OR, and add an IF for every condition, as such:

=ARRAYFORMULA(sum(IF(left(A1:A10;3)="GTR";VALUE(right(A1:A10;1));0);IF(left(A1:A10;3)="DBT";VALUE(right(A1:A10;1));0)))
0
votes

In addition to the solution of Kriggs, this formula should also work:

=ArrayFormula(sum(if(regexmatch(B3:B12, "GTR|DBT")=TRUE, right(B3:B12)+0,)))