Google Sheets SumIfs with left formula

Question

I want to use the sumifs formula, but the sum interval range has text in it.

Example:

|Criteria|Sum Interval|
|--------|------------|
| A      | 1 - Good   |
| A      | 2 - Regular|
| C      | 3 - Bad    |

So, I want to check the criteria field and, when met, sum the first character of the Sum Interval. I tried something like this: = sumifs( arrayformula(left(suminterval, 1)) , criteria, 'A')

In this case, the formula should return 3 (1 + 2)

• arrayformula(left(suminterval, 1)) = interval with only first character

This work when used alone, but when I use it as an argument, a receive a message saying that the argument must be a range.

Ps: The hole solution has to be in an only formula.

Answer 1

try:

=INDEX(QUERY({A2:A, REGEXEXTRACT(B2:B, "\d+")*1}, "select sum(Col2) where Col1 = 'A'"), 2)