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?
I tried everything but no result.
ReplyDeleteWhat version of Excel are you using?
ReplyDeleteI 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)
ReplyDeleteWorks: 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
Saud Hayat, did you ever find out how to use the date range and exclude zero values?? Thanks
DeletePam,
ReplyDeleteYou are close. see my post here for your solution...
http://excelprofessor.blogspot.com/2012/09/averageif-using-cell-reference-instead.html
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?
ReplyDeleteI 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!
Vikkybear, if you send me a sample of your xls I will see if I can find you a solution.
ReplyDeletegkflynn@hotmail(dot)com
That you! This is exactly what I needed
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteI have some data which looks like:
ReplyDeleteDate 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
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,.....
ReplyDeletehow to fix averageifs between date and multi criteria_range
ReplyDelete