Formula to count and divide only numbers <0 but not #NA

[merlyn45]

I need help with a formula that will sum up a column of percentages (A2:A29) and divide by a count of the numbers in the column that fit 2 criteria: Are >0 and <>#NA. (to get an average). This is the formula I have so far but it justs returns #N/A:
=(SUM(A2:A29)/COUNTIFS(A2:A29,">0",A2:A29,"<>#NA")). I think the problem is with summing the column with NA's in it...so I also tried: =(SUMIF(A2:A29,"<>#NA")/COUNTIFS(A2:A29,">0",A2:A29,"<>#NA")). Am I close?

[shg]

=SUMIF(A2:A29, ">0")/COUNTIF(A2:A29, ">0")

or

=AVERAGEIF(A2:A29, ">0")

[merlyn45]

Works great! thanks. I used the Averageif. I don't know why I thought I needed to use the #NA in the formula. Maybe because the original formula =sum(A2:a29)/countif(A2:A29, ">0") (which I did not write) worked fine until I changed the cell formulas in the data column to produce #NA's instead of 0's. I didnt not want the zeros on the chart, so I made them produce NA's instead. Thanks for your help