Google Sheets – Fix Query Divide by Sum Column Error

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'

I would write a custom function for this, to be used as

=combinations(table1!B2:B, table1!C2:C, table2!A2:A, table2!C2:C)

that is, the parameters are ValueColumn1, KeyColumn1, ValueColumn2, KeyColumn2.

The logic is entirely straightforward: run a double loop and collect whatever matches.

function combinations(range1, key1, range2, key2) {
  var output = [];
  if (range1.length != key1.length || range2.length != key2.length) {
    throw new Error('Each range must have the same length as its key'); 
  }
  for (var i = 0; i < range1.length; i++) {
    for (var j = 0; j < range2.length; j++) {
      if (key1[i][0] === key2[j][0] && key1[i][0] !== '') {
        output.push([range1[i][0], range2[j][0]]);
      }
    }
  }
  return output;
}