How to use Averageifs or Sumifs in an Arrayformula

Question

I'll post a link to the spreadsheet at the bottom of this write-up.

I'm trying to work out the average of a column of numbers that fall between a date range. The formula below works but I have to drag it down the column, I want it to be an array so it auto updates.

=iferror(averageifs(B$2:B,A$2:A,">="&C2,A$2:A,"<="&D2),1)

So I created it as an array as follows:

 =iferror(ArrayFormula(averageifs(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1

But it stops after the first cell. So I broke up the average function into sum & count in order to divide.

The count array works using this formula:

=iferror(ArrayFormula(countifs(A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1)

However the sum array does not even go past the first cell:

=iferror(ArrayFormula(SUMIFS(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1)

And the combination gets past the first cell but the calculations all come to zero:

=ArrayFormula(iferror(SUMIFS(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)/countifs(A$2:A,">="&C2:C,A$2:A,"<="&D2:D),1))

What I'm trying to achieve is an average for the previous month.

If anyone can help me I'd be very grateful!

https://docs.google.com/spreadsheets/d/1XhoLl5hB-MpXFz9VS2aLqJOWbXk1d_7apnwKWFdDUlg/edit?usp=sharing

Answer 1

you are trying to apply a bit weird structural logic with lots of repeating values with no reason. basically, it looks like you need this:

=ARRAYFORMULA(QUERY({TEXT(A2:A, "yyyy-mmm"), B2:B}, 
 "select Col1,avg(Col2) 
  where Col2 is not null 
  group by Col1 
  order by avg(Col2) desc 
  label avg(Col2)''", 0))

---

but if you really really need so:

=ARRAYFORMULA(IF(LEN(A2:A), 
 IFERROR(VLOOKUP(TEXT(DATE(YEAR(A2:A), MONTH(A2:A)-1, 1), "yyyy-mmm"), 
 QUERY({TEXT(A2:A, "yyyy-mmm"), B2:B}, 
 "select Col1,avg(Col2) 
  where Col2 is not null 
  group by Col1", 0), 2, 0), 0), ))