Average across sheets Google Sheets

Question

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?

Answer 1

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.

QUERY

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