AverageIf across a range of sheets

I am attempting to find the average of M6 across eight separate sheets. New sheets are frequently added so I'm doing the average of M6 between a range of sheets.

I first attempted to do a basic average function with a range like so:

Please Login or Register  to view this content.

Only 3 of the 8 cells have a value, however; it is dividing by 8 instead of 3, thus, giving me an incorrect average. I then attempted to do an averageif function that only calculated values above 0:

Please Login or Register  to view this content.

However, I'm returned with a #VALUE! error. (I've tried the CTRL + Shift + Enter with this as well)

The issue is - the remaining 5 cells that are being added into the total average calculation have no values within them. Their formula is "=K6/C6", however, there isn't a value for five of the eight cells. Is there any way around this? I've also tried SUMIF and COUNTIF, however; those return errors as well.

Any help would be appreciated.

If you have formula's in those cells, they have to be giving either a value, a 0 or an error (#/DIV). You can modify your formulas on the individual sheets with IF statements to remove errors.

You might want to remove those zeros also i.e. change your formulas to
=IF(OR(ISERROR(K6/C6),K6=0),"",K6/C6)
Does that help your cross the sheet calculations?