AVERAGEIFS to return an average between dates

I was asked to find the average sales between two date ranges. Well in earlier posts I described how to use SUMIFS and COUNTIFS to get the sum of values between two dates and to count the number of rows between two dates.
Well another cousin of SUMIFS and COUNTIFS is AVERAGIFS.
AVERAGEIFS allows you to get an average of a range of data base on specific criteria(s) (hence the IFS in AVERAGETFS).






In the above example I have a few columns of Data. I want the average total in Column C that have a date range between 1/5/2011 AND 1/20/2011. The formula is in Cell E2 but I show it to you in Cell D2.
=AVERAGEIFS(C:C,B:B,">1/5/2011",B:B,"<1/20/2011")
If I build the formula with the function Argument dialog box, it would look like the following…









Looking at the data sorted by date, I can see that my calculation is correct since the total of the cells highlighted in yellow is 18,000. When I divide that by 4, I get 4,500.






Some notes on the AVERAGEIFS function….
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Average_range is one or more cells to average, including numbers or names, arrays, or references that contain numbers.
Some samples of the AVERAGEIFS criteria are as follows….
45 – no quotes are required for numbers.
“Invoice” – needs quotes around it since it is text.
“>50” – needs quotes around it since it includes the > sign.
D7 – no quotes requires as it references a cell.
If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
This formula is not available in Excel 2003 or older.
How have you used AVERAGEIFS to resolve a problem?

12 comments:

  1. I tried everything but no result.

    ReplyDelete
  2. What version of Excel are you using?

    ReplyDelete
  3. I use Excel 2010. I can get it to work if i type in the dates manually, but i want to have a start date, end date and have the formula feed off that - and it doesn't work / may just go to Access (sigh)
    Works: AVERAGEIFS(J5:J2172,A5:A2172,">1/1/2012",A5:A2172,"<8/12/2012")
    Fails: #Div/0! AVERAGEIFS(J5:J2172,A5:A2172,">b2175",A5:A2172,"=b2175",A5:A2172,"<=b2176")
    J5:J2172 = range
    A5:A2172 = dates to look at
    b2175 = start date
    b2176 = end date

    ReplyDelete
    Replies
    1. Saud Hayat, did you ever find out how to use the date range and exclude zero values?? Thanks

      Delete
  4. Pam,

    You are close. see my post here for your solution...

    http://excelprofessor.blogspot.com/2012/09/averageif-using-cell-reference-instead.html

    ReplyDelete
  5. I'm having an issue very similar...I have a column with the "days to closure" already, so I can use that data, but my snag is that I need to break out the results by client. I have about 5 repeating clients, so I want to report the Average Days to Closure by client. Every formula I have tried doesn't work. Any suggestions?
    I should add that this is a large spreadsheet, about 380 rows (each row is a project with a start/end date) and data gets added every day...new projects, that is.
    Thanks!

    ReplyDelete
  6. Vikkybear, if you send me a sample of your xls I will see if I can find you a solution.

    gkflynn@hotmail(dot)com

    ReplyDelete
  7. That you! This is exactly what I needed

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. I have some data which looks like:

    Date Flux
    1.1.13 3
    1.1.13 4
    3.3.13 10
    3.3.13 9
    3.3.13 4
    .
    .
    .
    Now I want an average flux value against each date but don't know how to set the criteria using averageifs function. Please note each date doesn't have the same number of flux values. Your help will be highly appreciated. Thanks

    ReplyDelete
  10. why is it every online reference i see has the parameters in the order AVERAGEIFS(average_range,criteria_range1,criteria1 ...... but my copy puts them in the order AVERAGEIFS(average_range, criteria1 , criteria_range1,.....

    ReplyDelete
  11. how to fix averageifs between date and multi criteria_range

    ReplyDelete