Average same cell, across multiple sheets, exclude zeros, Google Sheets

[Drew_]

Hi all,

I've looked on the Google Forums and even started a thread there and never got a response. I've had much success on here so thought I'd ask.

I have 2 questions actually. The first: I want to average the same cell from multiple tabs and put it in a master tab that will update automatically. I have a simple formula that works, but it does not exclude zeros, so I am getting the wrong numbers:

=AVERAGE('Sonoma State'!C2,'Chico State'!C2,CSUMB!C2)

It is just C2 from three different tabs. I tried AVERAGEIF and AVERAGEIFS and couldn't get it to work.

The second: I am trying to pull data from several Google Sheets and put it all into one master sheet so that whenever entries are made on the separate sheets, they are automatically updated on the master sheet. I got a formula to return data, however it is incorrect:

=IF(IMPORTRANGE("1pwT7vO2aPoG2XD2ne_3hP8nasbbFVwPRPmBZpkQkk8A/edit#gid=936503833","Averages!$A$2:$A$61")="Sonoma State",SUM(IMPORTRANGE("1pwT7vO2aPoG2XD2ne_3hP8nasbbFVwPRPmBZpkQkk8A/edit#gid=936503833","Averages!E2:E61")),0)

I think I have to use the QUERY function but I'm not sure how to do that. I'm hoping someone here knows Google Sheets better than me and can help, thanks