So lets say I have sheet1

a | 4
b | 5
c | 33
d | 55

and sheet2

a | 1
b | 2
c | 3
e | 2

And on sheet3 I want to get it in this kind of format.

name | average | number of times referenced?
a         2.5              2
b         3.5              2 
c         18               2  
d         55               1
e         2                1

How would I go about doing this?


Please try something like:

=query({Sheet1!A1:B10;Sheet2!A1:B10},"select Col1, Avg(Col2), count(Col1) where Col1 is not NULL group by Col1 label Col1 'name', count(Col1) 'number of times referenced?', Avg(Col2) 'average'")

Adjust ranges to suit those you did not mention.


For your sample not necessary but you might want to insert order by Col1 asc before label.

| improve this answer | |
  • Just wondering though If I wanted to be able to sort by second or third column without changing the sorting in the query how could I? At the moment it sorts it but then goes straight back to column 1 being sorted. – John Sumner Nov 15 '17 at 14:58
  • webapps.stackexchange.com/questions/111857/… there is the new question – John Sumner Nov 15 '17 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.