How to use countif/if/average

QuestionEdit

QUESTION: I am trying to get a formula that will give me the average of entire column based on one definite condition(as: if a='completed' then perform average of all rows which equal "Jan/2010". Are you able to help me with this? I hope I didnt confuse you :).

The end result will be an average of entire column where all have been completed and all equal to Jan/2010.

My current formula is: =ROUND(AVERAGEIF('Reports - All'!$AW:$AW,"Jan/2010",'Reports - All'!$AX:$AX)*('Changes - Reports - All'!$B:$B="Completed"),0)

ANSWER: You have the syntax wrong. First parameter is range to be averaged, which is column AW of the sheet named "Reports - All" Then you list PAIRS of ranges/criteria First pair: 'Reports - All'!$AX:$AX and "Jan/2010", 2nd pair: 'Changes - Reports - All'!$B:$B and "Completed" (ensure that there's a sheet named "Changes - Reports - All"

Complete formula should be =ROUND(AVERAGEIF('Reports - All'!$AW:$AW,'Reports - All'!$AX:$AX,"Jan/2010",'Changes - Reports - All'!$B:$B,"Completed"),0)

No need for the asterisk you had in your formula.

---------- FOLLOW-UP ----------

QUESTION: When I tried your formula I get an error for too many arguments at the $B:$B part. Any suggestions?

ANSWER: Change AVERAGEIF to AVERAGEIFS

---------- FOLLOW-UP ----------

QUESTION: This is not working, it gives me result #DIV/0. The original formula provides the actual average. (checked it manually)

My original formula worked but I want to add an additional condition which doesn't seem to work.

Any other suggestions?

AnswerEdit

There's nothing wrong with the formula. You'd get #DIV/0 when the total values for the conditions are 0. The formula as written finds all occurrances of "Jan/2010" (spelled exactly like that, not a real date, but text) in Reports-All in AX and in those same rows in Changes-Reports-All column B that say "Completed". The result is saying there are NO cells with numbers which correspond. If you say there are, feel free to send me the wb and I'll take a look, but the formula doesn't lie. -- send to bobumlas@yahoo.com, use a subject of "AllExpertsQ". Is your date Jan 2010 or the text value "Jan/2010"? Big difference.