How to use AVERAGEIFS within ARRAYFORMULA

Question

I am trying to use AVERAGEIFS inside ARRAYFORMULA. Looking at other questions, I have come to the conclusion that it is not possible without using QUERY function.

My intention is to average the values of a column whenever they share the same ID.

I think this question comes pretty close to what I need, but I haven't been able to replicate and adapt its solution on my own sheet.

In this sheet I show the result I expect (I got it by dragging the formula). I've also reviewed the Query Language Reference, unsuccessfully.

Thanks a lot for your time and effort.

In your sheet it works out the average for the current ID only from the current row onwards (so 2019_1_JOHN changes from 16.7 to 22.5 to 25) - can you confirm this is what you are looking for plz? — Tom Sharpe, Oct 24 '19 at 17:17
Tom is right, that was a mistake creating dummy data and it's fixed now. I'm sorry... — lozgarrido, Oct 24 '19 at 17:44
So to best of my knowledge you can't do it with averageif, but can do it with sumif/countif — Tom Sharpe, Oct 24 '19 at 18:04

Tom Sharpe · Accepted Answer · 2019-10-25T07:38:57.263

So the formula should be

=ArrayFormula(iferror(sumif(A2:A,A2:A,B2:B)/countif(A2:A,A2:A)))

Note that if there were any text values in the points column, this would still return a result (because count would be greater than zero) - you could instead use

=ArrayFormula(if(isnumber(B2:B),(sumif(A2:A,A2:A,B2:B)/countif(A2:A,A2:A)),""))

If you had a mixture of rows with text and rows with numbers for any ID, this would return a smaller result than the avg or average formula. This is a limitation of this method. You can't put an extra condition in (that column B has to contain a number) because you would need countifs and countifs isn't array-friendly. It still seems strange that AFAIK countif and sumif are the only functions out of this family that are array-friendly while countifs, sumifs, averageif etc. are not.

score 2 · Answer 2 · answered Oct 24 '19 at 18:36

2

you can do:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; QUERY(A2:B; "select A,avg(B) group by A"); 2; )))

answered Oct 24 '19 at 18:36

player0

124,011
12
67
124

1

Thank you so much! Both your answer and Tom's are working perfectly for me. I gave you upvotes, but it seems I don't have reputation enough to be displayed. Anyway, thank you again for your time. – lozgarrido Oct 24 '19 at 19:37

How to use AVERAGEIFS within ARRAYFORMULA

2 Answers2

Linked