# Sum cells unless a cell = 0



## RodneyC (Dec 22, 2022)

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 (Dec 22, 2022)

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

or just

=AVERAGE(E4:Q4)

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


----------



## etaf (Dec 22, 2022)

how about

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


----------



## xlhelp15 (Dec 22, 2022)

RodneyC said:


> I'm using this formula
> 
> =SUM($E4:$Q4)/12
> 
> ...


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


----------



## Joe4 (Dec 22, 2022)

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 (Dec 22, 2022)

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


----------



## etaf (Dec 22, 2022)

you are welcome


----------



## Peter_SSs (Dec 23, 2022)

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

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


----------

