Google-sheets – How to SUM on all column based on their own key

I think, you need the combination of formulas. The answer is:

={QUERY({A:C},"select Col1, sum(Col2) where Col1 <>'' group by Col1"),{"filtered sum";ArrayFormula(IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")),QUERY({A:C},"select Col1, sum(Col2) where Col3 ='yes' group by Col1") ,2,0),0))}}

Explanation

It's not hard if you'll take it by parts:

• ={basic query, {"header"; vlookup(a, help query, 2, 0) }}

Basic query

QUERY({A:C},"select Col1, sum(Col2) where Col1 <>'' group by Col1")

It's simple, I've used Col1, Col2... notation to make it work with any range.

Vlookup

IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")), help query ,2,0),0))

We count sums with criteria (c = 'yes') in the help query.

UNIQUE(FILTER(A2:A,A2:A<>"")) part of the formula gives you a list from column 'a'.

Help query

QUERY({A:C},"select Col1, sum(Col2) where Col3 ='yes' group by Col1")

Here you may enter any conditions what you want. In this case it's Col3 ='yes'

Several Google Sheets built-in functions like SUM require that their arguments be numbers and if some of them are text in most of the cases they will return an error message. Exceptions are text values that could be coerced to numbers like numbers quote enclosed.

There are several built-in functions that could be used to handle unknown cell value types

• IFERROR If the first argument returns an error, returns the second argument
• ISNUMBER Returns true if the argument is a number value, false otherwise
• ISTEXT Returns true if the argument is a text value, false otherwise

the question doesn't include the expected result when the cells have a text value.

Let say that the expected result is 0, then we could use the following formula:

=IFERROR(SUM(D2,H2,M2),0)