Stuck with SUM formula

**JRose120** · 05-29-2009, 08:39 AM

This should be easy for you all, but I am stuck.

I am using this equation...

=SUMIF(M3:AB3,">0",M3:AB3)/COUNTIF(M3:AB3,">0")

to do this...

Cell M3 through AB3 is a series of drop downs. the list included these values...

n/a
25%
50%
75%
100%

All I want to do is SUM up the range, and if there is an "n/a" in that range, I want to ignore it and calculate the total percentage.

I keep getting: #DIV/0! error.

**NBVC** · 05-29-2009, 08:49 AM

Not sure what you mean... that formula and the SUM() function will ignore the n/a 's

Maybe you want:

=IF(SUM(M3:AB3),SUMIF(M3:AB3,">0")/COUNTIF(M3:AB3,">0"),0)

if there are no values at all in the range or if 0's only..

**JRose120** · 05-29-2009, 09:07 AM

Thank you for the help; it did not work.

Cell M3 through AB3 have percent values, I need to sum them all up, average them to make no more than 100% and exclude the "n/a" value if that value exists in that range.

EX:

#DIV/0! n/a 25% 50%

The "DIV" should show the total average percentage.

**NBVC** · 05-29-2009, 09:14 AM

With those 3 entries and the remainder blank.. I get 0.375 or 37.5%

Can you post your workbook showing how this is not working? Maybe I am missing something.