Sum cells unless a cell = 0

[RodneyC]

I'm using this formula

=SUM($E4:$Q4)/12

to sum the values in a row. For some of the rows, some of the values in E:Q are 0. If that is the case, I want the value 12 to change to however many values E:Q are greater than 0. Example, if only 8 of the values E:Q were greater than 0 then the formula would change the 12 to an 8.

Make sense?

Thanks in advance

 

[Z51]

=SUM(E4:Q4)/COUNT(E4:Q4)

or just

=AVERAGE(E4:Q4)

Also, E:Q is 13 columns. Not sure that makes a difference.

 

[etaf]

how about

=SUM($E4:$Q4)/COUNTIF($E4:$Q4,">"&0)

 

[xlhelp15]

I'm using this formula

=SUM($E4:$Q4)/12

to sum the values in a row. For some of the rows, some of the values in E:Q are 0. If that is the case, I want the value 12 to change to however many values E:Q are greater than 0. Example, if only 8 of the values E:Q were greater than 0 then the formula would change the 12 to an 8.

Make sense?

Thanks in advance

This can be too...
=SUM(E4:Q4)/COUNTIFS($E$4:$Q$4,"<>0")

 

[Joe4]

Also bear in find that if you do not have any values in columns E:Q, you will get a #DIV/0 error message.

You can fix that by wrapping whatever formula you choose to use in an IFERROR function, i.e.
=IFERROR(your formula,"")

 

[RodneyC]

thanks everyone, I went with this becauase it was the first one I saw: =SUM($E4:$Q4)/COUNTIF($E4:$Q4,">"&0)

 

[etaf]

you are welcome

 

[Peter_SSs]

What about the single function designed exactly for that sort of thing?

=AVERAGEIF($E4:$Q4,">0")