How to use the SUMIFS function in an ARRAYFORMULA, matching two criteria, to avoid copying down?

Question

Update: This is a duplicate question - see : Google Sheets ArrayFormula with Sumifs

I am raising this question because someone asked it today in a poorly structured question, and theirs was blocked for answering. A similar question was answered previously on SO, but it did not involve separate criteria columns. I'm just trying to offer an answer that I came across while researching the question.

The question was how to do an SUMIFS, (and an AVERAGEIFS) comparing a range to two other columns, without having to do a dragdown of an SUMIFS type formula. Here is what the data looks like, in columns A:C. The criteria to match are in columns F and G.

Answer 1

The formula I came across uses SUMIF, instead of SUMIFS. It is in cell K1.

={"Sum by Array";ARRAYFORMULA(IF(G2:G<>"",SUMIF(A2:A&B2:B , F2:F&G2:G , $C$2:$C),""))}

The use of an outer array, and the initial string, "Sum by Array", is to allow this to be placed in the header row. The two initial columns in the data range, A:B, are concatenated, and compared to the two concatenated columns in the criteria range, F:G. Then SUMIF sums the values that match each criteria row.

As an example of a more complicated way, I first came up with this formula, which also works:

={"Sum by Array";
  ARRAYFORMULA(IF(LEN(G2:G),IFNA(
      VLOOKUP(G2:G& " " & TEXT(F2:F,"dd/mm/yyyy"),
      TRANSPOSE(
        QUERY(TRANSPOSE(
          QUERY($A$2:$C, "select B, A, sum(C) group by B,A label sum(C) '' ",0)),
          "select *",2)),2,0),"blank"),""))}

An important note - the value "2" in the last select statement above, "select *",2, is the number of columns being concatenated, ie the number of criteria columns, 2 in this case.

I haven't got AVERAGEIF to work the same way - still looking at that. Using the SUMiF result divided by a matching COUNTIF value should work. Or the above query works, by replacing "sum(C)" with "avg(C)".

Answer 2

why not:

=QUERY(A2:C, 
 "select A,B,sum(C),avg(C) 
  where A is not null
  group by A,B
  order by B
  label sum(C)'',avg(C)''")