1. ## Help With AVERAGIF

Hello everyone,

I created a tracking spreadsheet for a facility to determine opportunities in space utilization. I created weekly, monthly, and daily averages (for specific time frames). This tracking method relies on staff doing rounds and head counts which, unfortunately, doesn't happen as consistently as I would prefer leaving zeros or empty spaces in the input sheets. I would like to change the formula from a regular =AVERAGE to an =AVERAGEIF that only considers values greater than 0. I tried multiple variations based on other blog posts but nothing seems to work. Here is the formula I am trying to fix:

=AVERAGEIF((Mon!B5:D7,Tue!B5:D7,Wed!B5:D7,Thu!B5:D7,Fri!B5:D7,Sat!B5:D7,Sun!B5:D7),">0")

As you can see, I'd like to take the average of a range (B5:D7) from multiple sheets (Mon!, Tue!, Wed!, etc) of all values greater than zero. I have also tried "<>" as the condition for to exclude empty cells but this didn't work either.

What should I try to make this work?

2. With data values to average in A1:A4 cells on Sheet1, 2 and 3:
Put the below formula somewhere in Sheet1. Adjust the formula accordingly if you want the result in a different sheet.

=(SUM(A1:A4)+SUM(Sheet2!A1:A4)+SUM(Sheet3!A1:A4))/(COUNTIF(A1:A4, "<>"&0)+COUNTIF(Sheet2!A1:A4,"<>"&0)+COUNTIF(Sheet3!A1:A4, "<>"&0))

3. Worked! Thank you!

4. Actually, after I started entering some data I realized it was simply adding everything and not taking the average. It's as if its dividing by 1. Do you know why this is?

5. Never mind. I figured it out. I was just missing some brackets.

6. Glad you solved it, and sorry Ive only just picked up your query. I used to get an email telling me about replies, but its not working any more.