AVERAGEIF a value appears in other columns of a row in Google Sheets - Stack Overflow

admin管理员组

文章数量:1122832

I am trying to use AVERAGEIF, or a similar function, to find the average of values in a column if a name appears elsewhere in the row. Normally this is a straightforward use of AVERAGEIF, but the name could appear in a few different columns within the row.

Attached is a toy example.

Score	Players
3	Kylie	Anna
4	Anna	Lois	Michelle
5	Michelle

I am trying to use AVERAGEIF, or a similar function, to find the average of values in a column if a name appears elsewhere in the row. Normally this is a straightforward use of AVERAGEIF, but the name could appear in a few different columns within the row.

Attached is a toy example.

Score	Players
3	Kylie	Anna
4	Anna	Lois	Michelle
5	Michelle

Here, I want the average score column to be the average of the scores in the rows in which the players appear. For instance, Anna appears in rows 2 and 3, so I want it to calculate the average of the scores in rows 2 and 3.

This is what I want the spreadsheet to show:

Name	Count	Average Score
Anna	2	3.5
Kylie	1	3
Lois	1	4
Michelle	2	4.5

In case it matters, the real spreadsheet has more columns that could contain the names, but they shouldn't count outside of the specified columns. so I do need it to restrict to certain columns, not just anywhere in the row.

I got the intended results part to automate the names by doing =sort(UNIQUE(FLATTEN(B2:D4))) (where B2:D4 is the range of the players) in A7 and the appearance count with =countif(B$2:D$4,A7) in B7. The average score is where I am running into issues.

In C7, I tried doing =averageif(B$2:D$4,A7,A$2:A$4) and extending it downward, but instead of what I wanted, I got the following.

Name	Count	Average Score
Anna	2	4
Kylie	1	3
Lois	1	#DIV/0!
Michelle	2	5

It seems like for Anna and Michelle it only took the second score that applies to them instead of the average, but I can't figure out why it just gave me an error for Lois at all.

• google-sheets

Share Improve this question Follow asked yesterday hcm13hcm13 1311 silver badge33 bronze badges New contributor hcm13 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

You can't use Averageif because the dimensions of the criteria range (B2:D4) must match the dimensions of the range of numbers to be averaged (A2:A4). Your formula is just using the first column of B2:D4 as criteria: Lois doesn't appear in the first column so her result is #DIV/0!

In principle you could duplicate A2:A4 three times like this {A2:A4,A2:A4,A2:A4} to match the dimensions of B2:D4, but it doesn't work because this would be an array and Averageifs expects a range.

A simple way to get round this (providing each name can only occur once per row) is to use array multiplication likes this to get the sum and divide by the count:

=ArrayFormula(sum(A$2:A$4*(B$2:D$4=A7)))/countif(B$2:D$4,A7)

Score	Players
3	Kylie	Anna
4	Anna	Lois	Michelle
5	Michelle
Name	Count	Average Score
Anna	2	3.5
Kylie	1	3
Lois	1	4
Michelle	2	4.5

You may try:

=averageif(byrow(B:D,lambda(Σ,xmatch(F4,Σ)^0)),1,A:A)

Another approach to get the whole output_table in one go:

=let(Σ,reduce(,B2:index(D:D,match(,0/(A:A<>""))),lambda(a,c,vstack(a,{c,index(A:A,row(c))}))),
 query(Σ,"select Col1,count(Col1),avg(Col2) where Col1!='' group by Col1 label count(Col1) '',avg(Col2) ''"))

本文标签： AVERAGEIF a value appears in other columns of a row in Google SheetsStack Overflow