Averageif Across Multiple Cells in Different Sheets

Hello,

I would love your help. I am trying to get an average of two cells (on cell in one sheet and one cell in another sheet). The problem I am running into is that I can not have the number 0 included in the average. I am working with times and a 0 really throws that data.

Here is the Averageif formula I used: =AVERAGEIF(T65,'8'!T68,">0:00")

Just add them up and use COUNTIF not zero instead as the divider.

Try this:

=SUM(T65,'8'!T68)/SUMPRODUCT(IF(T65>0,1)+IF('8'!T68>0,1))

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

@Glenn: whyfor the SUMPRODUCT? Why not just

Formula:

Please Login or Register  to view this content.

Too much coffee... not enough beer... take your pick. For me it's now evening, so am about to remedy the latter.

Or, for brevity, just:

Formula:

Please Login or Register  to view this content.

Go for the beer ... where are you? I didn't think NIwas that far ahead of us