[SOLVED] AVERAGEIF ignoring 0%

[fabrecass]

hi,

column A contains months of the year, column Q contains percentage scores.

I want the formula to look for a specific month and average the corresponding scores in column Q but ignore any 0% scores.

I suck at nesting IF statements (if that's the correct solution) but this is what i have so far

Please Login or Register  to view this content.

I've been playing around with "<>0" but I can't get it to work properly, help?!

[davsth]

=AVERAGEIFs($Q:$Q,$A:$A,"May",$q:$q,"<>0")

[JohnTopley]

Try

=AVERAGEIFS($Q:$Q,$A:$A,"May",$Q:$Q,"<>0" )

[fabrecass]

=AVERAGEIFs($Q:$Q,$A:$A,"May",$q:$q,"<>0")

I'm getting "you've entered too many arguments for this function".

Does the fact that the formula will be on a different worksheet matter? Sorry I thought i was keeping it simple by removing the worksheets from the formula

=AVERAGEIF('Alex Brown'!$Q:$Q,'Alex Brown'!$A:$A,"May",'Alex Brown'!$Q:$Q,"<>0")

This is what I used based on your solution to get the "you've entered too many arguments for this function" error. Am I doing something stupid?

[davsth]

look again, slightly stupid, but we all do it, you just missed an S, that's why the number column is now first. the same applies with countif(s) and sumif(s)
=AVERAGEIFs($Q:$Q,$A:$A,"May",$q:$q,"<>0")

=AVERAGEIFS('Alex Brown'!$Q:$Q,'Alex Brown'!$A:$A,"May",'Alex Brown'!$Q:$Q,"<>0")

[fabrecass]

Thank you so much! worked perfectly. Knew I was doing something stupid.