Formula and approach to problem

[gilbert]

I have a sheet for house remodeling expenses. It is hard to explain what I am trying to do.
Col A house addresss
Col B has square footage
Col C starts the various expense such as cabinets then D has painting and E has Plumbing

What I am trying to do is if I have 20 houses and I get to col C for house number 1 I tell it to divide the cost for painting that house by the sqft of that house to get a per sqft cost to paint. But I need for the painting col to sum all of the painting costs for all 20 houses which it does but I want it to only divide by the number of houses that I actually painted because some I did not paint. I mean if I only needed to paint 5 of the houses then the total cost of the painting for those 5 then gets divided by 20 houses. How can I get the sheet to ingore totaling the houses that have 0.00 in the cell meaning no paint cost is associated with this house.

Thanks for any help.

Galen

 

[Aladin Akyurek]

=AVERAGE(IF(Range,Range))

which must be entered by using control+shift+enter, not just enter.

Equivalently...

=SUM(Range)/MAX(1,COUNT(Range)-COUNTIF(Range,0))

which is ordinarily entered.