AVG Ignore Zero - Non adjacent cells

Hello,
So, I need to do an average of cells:
WeeklySummary!BZ32:BZ36,WeeklySummary!DD32:DD36

but not include zeros in the average -

I tried
=AVERAGE(IF(WeeklySummary!BZ32:BZ36,WeeklySummary!DD32:DD36>0,WeeklySummary!BZ32:BZ36,WeeklySummary!DD32:DD36)) hitting ctrl shift enter and I get a msg saying I've entered too many arguments.

Meanwhile, in another cell, I am able to use:
=AVERAGE(IF(WeeklySummary!EH32:EH36>0,WeeklySummary!EH32:EH36)) hitting cntrl shift enter just fine - just can't seem to do it with multiple cell ranges.

And for the record, this is an average of an average, but the person who needs the data would rather have it that way since the amount percentage of total waste, which is total feet, minus actual feet divided by total feet. I'm open to setting it up to have the actual average, but there again, it would be adding a subtracting a large number of cells and ignoring blank or zero cells.

Thank you.

Try:

=SUM(SUMIF(INDIRECT({"WeeklySummary!BZ32:BZ36","WeeklySummary!DD32:DD3"}),">0"))/SUM(COUNTIF(INDIRECT({"WeeklySummary!BZ32:BZ36","WeeklySummary!DD32:DD3"}),">0"))

Originally Posted by sabrinigreen

This worked! thanks (I had to change the second DD cell reference to DD36, but yeah, it worked ;-)

ooops.. i copied the reference without the final 6..

I'd suggest you do two x SUMIF and two x COUNTIF greater than zero. Add the two SUMIFs and add the two COUNTIFs and divide the latter into the former.

Regards

I guess I was just being a bit lazy. What I meant was, do two SUMIFs and add them together and two COUNTIFs and add them together, and then divide the total SUMIFs by the total COUNTIFs.

=(SUMIF(WeeklySummary!BZ32:BZ36,">0")+SUMIF(WeeklySummary!DD32:DD36,">0")) / (COUNTIF(WeeklySummary!BZ32:BZ36,">0")+COUNTIF(WeeklySummary!DD32:DD36,">0"))

Regards