Averageif, not counting zeros, but in multiple ranges)

I need to return an average for the following ranges E5, E9:13, E17:21, E25:29, E34:37 only and not count any zeros. I have tried multiple options with sum/countif, averageif, etc., and I must be missing something. If there a solution? TIA

have you just tried =AVERAGE(E5, E9:E13, E17:E21, E25:E29, E34:E37)? The average function ignores blanks.

corrected for clarity

I tried this on some data with zeros and blanks and it appeared to work, maybe you can adapt it to your range?
=AVERAGEIF(H583:H590,"<>0",H583:H590)

EDIT: and it also works without the second range... =AVERAGEIF(H583:H590,"<>0")

without seeing your data this is the latest I could come up with that works in my sample data. I know the items included are contiguous but they were entered as ranges, see if it works for your ranges...
=SUM(E5,E9:E13,E17:E21,E25:E29,E34:E37)/(COUNTIF(E5,"<>0")+COUNTIF(E9:E13,"<>0")+COUNTIF(E17:E21,"<>0")+COUNTIF(E25:E29,"<>0")+COUNTIF(E34:E37,"<>0"))

EDITed, adjusted to your ranges.

Great, glad that worked for you. If solved please mark your post as solved using the thread tools dropdown at the top of the post.
and if you're inclined clicking on *Add Reputation helps us advance on this forum.

Originally Posted by Sambo kid

=SUM(E5,E9:E13,E17:E21,E25:E29,E34:E37)/(COUNTIF(E5,"<>0")+COUNTIF(E9:E13,"<>0")+COUNTIF(E17:E21,"<>0")+COUNTIF(E25:E29,"<>0")+COUNTIF(E34:E37,"<>0"))

Your ranges need to be fully populated (with numbers) for that formula to work, though, because blank cells, for example, will be counted by the COUNTIF functions


This version will work assuming you don't need to accommodate negative values, will also handle blanks

=SUM(E5,E9:E13,E17:E21,E25:E29,E34:E37)/INDEX(FREQUENCY((E5,E9:E13,E17:E21,E25:E29,E34:E37),0),2)

It's possible, if you want, to give your discontiguous ranges a single name, and then use the name only, i.e.

=SUM(Range1)/INDEX(FREQUENCY(Range1,0),2)