IF ERROR Formula when trying exclue zeros from average
I am creating a sheet for PM weekly time tracking. The top parent row (blue) is YTD roll up of total hours, as well as a column for average weekly hours (< this is my issue but I'll to that shortly). I have a child row (green) for each quarter, and then grandchildren rows (white) for each work week. The child rows also have roll up totals/averages for the grandchild rows (see 1st pic).
My first problem was calculating the weekly averages in the Total Hours column, excluding any zeros in the future weeks that haven't occurred yet  so I used the following formula in each child row to get an average for each quarter (see 2nd pic):
=SUM([Total Hours]3:[Total Hours]15) / (COUNT([Total Hours]3:[Total Hours]15)  COUNTIF([Total Hours]3:[Total Hours]15, 0))
This formula worked perfectly and gave me accurate averages at each child row. My plan was to have to have an average roll up at the parent row; however, for the child rows that don't have any entries in the grandchild rows, I am getting the error #DIVIDE BY ZERO, which of course results in the same error in the parent row. So I tried adding an IFERROR fx as follows:
=IFERROR(=SUM([Total Hours]17:[Total Hours]29) / (COUNT([Total Hours]17:[Total Hours]29)  COUNTIF([Total Hours]17:[Total Hours]29, 0)), 0)
This removed the error, but now I am getting a result of "0" even in the child rows where there was no error before (see 3rd pic).
I would greatly appreciate any alternative solutions!
Comments

Hi there,
Just remove the = before the SUM function and you should be sorted. You are getting a 0 for all values as the formulas is evaluating the whole thing as an error (even before you get to the issue of 0 rows) and therefore returning an error.
Change your last 0 to "my formula is broken" and you'll see what I mean .
Kind regards,
Chris McKay
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!