Need help with an If and Sumif statement

Hello all,
Thanks in advance for any help. Not exactly an Excel newbie but not a guru either. I'm having trouble with a formula. I want to make sure that the countif (which is essentially the denominator in the equation has a value of > 0, else render 'n/a'. I'm having trouble writing this. If I take out the "n/a" at the end, I will get a value. The sumif/countif formula is ok on its own. It's when I try to incorporate it into an if statement that I'm having my problems. Hopefully I've explained this well enough.

=IF(COUNTIF(AllReports.$A$4:$A$500,$A4)>0,SUMIF(AllReports.$A$4:$A$500,$A4,AllReports.C$4:C$500))/COUNTIF(AllReports.$A$4:$A$500,$A4),"n/a")

Thanks,
John

Originally Posted by etaf

perhaps it a country version of excel

AllReports.$A$4:$A$500

should that read
AllReports!$A$4:$A$500

Thanks for the reply. I'm not sure that's it. If I use the formula: =(SUMIF(AllReports.$A$4:$A$500,$A4,AllReports.D$4:D$500))/COUNTIF(AllReports.$A$4:$A$500,$A4), I get the correct answer. If I try to use this in an If statement, conditional on the results of the COUNTIF, I get an error.

It's ok. I had a misplace bracket.

Originally Posted by etaf

what do you get from this test
COUNTIF(AllReports.$A$4:$A$500,$A4)>0

true false
?

you have an extra bracket closing the IF here

,SUMIF(AllReports.$A$4:$A$500,$A4,AllReports.C$4:C$500))/COUNTIF(AllReports.$A$4:$A$500,$A4)

try
,SUMIF(AllReports.$A$4:$A$500,$A4,AllReports.C$4:C$500)/COUNTIF(AllReports.$A$4:$A$500,$A4)

Thanks Wayne, I found that as well.