# How to use countif/if/average

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:41 PM GMT

## Question

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)

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

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

---------- 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?