= find B12 in 1:1 and return average of numbers in that column where A13 matches in A:A

[Schultze]

That is an awful title, I know. Sorry

I've attached a little image that might be more explanatory.

I'm looking for the formular to fill in B13:D15.

As far as I've gotten is: ArrayFormula(IFERROR(SUMIFS(B2:B8,A2:A8,$A13)/COUNTIF(A2:A8,$A13),""))

And the formular for B12:D12 is: ArrayFormula(IFERROR(INDEX(1:1,SMALL(IFERROR(COLUMN(12:12)/(ISNUMBER(SEARCH("Category",1:1))),FALSE),COLUMN(A:A))),""))

I guess somewhere down the line these have to be merged... But how and where?!

PS. this is Google Sheets so AGGREGATE doesn't work.

Anybody?

Kindly,
Rasmus

Formula.png

[Pete_UK]

Do you have the AVERAGEIFS function in Google sheets?

Pete

[Jonmo1]

Try

=AVERAGEIF($A$2:$A$8,$A13,INDEX($B$2:$F$8,0,MATCH(B$12,$B$1:$F$1,0)))

[Schultze]

Pete_UK - there is
Jonmo1 - YES! Much more simple than was I was getting myself into. Trees and forests, you know...

Cheers!!

[Pete_UK]

Jonmo1 beat me to it.

Glad you got your solution.

Pete