averageifs exclude blanks and zeros

[paula.mccall]

good afternoon,

I am trying to use an "averageifs" formula, but exclude any blanks or zeros from the average. I have tried using the following:

AVERAGEIFS('Jan-Dec 12'!Y:Y,'Jan-Dec 12'!C:C,Counts!$K$1,'Jan-Dec 12'!AE:AE,Counts!$M$1,"<>0"). I keep getting a "too few arguments" message.

Attached is the workbook I am using - see the "Counts" Tab, column G

Can someone please show me what I am doing wrong.

I appreciate the help!

Kind Regards,

Paula

[zbor]

Add one more criteria to exclude zero:

=AVERAGEIFS('Jan-Dec 12'!Y:Y,'Jan-Dec 12'!C:C,Counts!$K$4,'Jan-Dec 12'!AE:AE,Counts!$M$1,'Jan-Dec 12'!Y:Y,"<>0")

[zbor]

To avoid K and M columns you could use:

=IF(F2="","",AVERAGEIFS('Jan-Dec 12'!Y:Y,'Jan-Dec 12'!C:C,LOOKUP(2,1/($E$1:$E2<>"YTD Avg Efficiency:")/ISTEXT($E$1:$E2),$E$1:$E2),'Jan-Dec 12'!AE:AE,F2,'Jan-Dec 12'!Y:Y,"<>0"))

(Make sure to enter values in E column and then you can remove K and M)

[HansDouwe]

Or replace in 'Jan-Dec 12'!Y:Y the zeros with blanks, because blanks are excluded automaticly.

[paula.mccall]

thank you both soooo much - i was able to use either of the two formulas to make this work. I greatly appreciate your help and time!