Formula to ignore negative numbers in a column?

[Guest]

I have a lengthy spreadsheet with several columns of numerical data. Some
cells have positive numbers and others have negative numbers. My goal is to
get both a column sum and average--but only of only those cells with a
positive number--i would like to exclude the negative numbers from my
calculations.

Are there formulas that will so this for me?

 

[Bernie Deitrick]

Tia,

Sum:
=SUMIF(D,">0")

Average:
=SUMIF(D,">0")/COUNTIF(D,">0")

HTH,
Bernie
MS Excel MVP

 

[Guest]

Try something like this:

With values in cells A1:A10

Greater than zero:
SUM
B1: =SUMIF(A1:A10,">0")
AVERAGE
B2: =SUMIF(A1:A10,">0")/COUNTIF(A1:A10,">0")

Greater than or equal to zero:
SUM
B1: =SUMIF(A1:A10,">=0")
AVERAGE
B2: =SUMIF(A1:A10,">=0")/COUNTIF(A1:A10,">=0")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Dana DeLouis]

Another option for Average using an Array formula:
=AVERAGE(IF(Rng>0,Rng))

Rng being a range name of say A1:A10