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

Sir, I have a data set containing daily stock prices from 1-Jan-00 to 30-Jun-16. Now i have to calculate annual average of returns for the fiscal year 2-Jul-04 to 30-Jun-05, 2-Jul-05 to 30-Jun-06, and so on. And i also want exclude zero values from returns. solution

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,.....

ReplyDeletePPC Expert For Tech Support |inbound calls for tech support, pop up calls for tech support Call at: +91 981-142-0083 ppc management experts ppc call provide by osiel web

ReplyDeleteexpertppc

Bros, it works as follows:

ReplyDelete=AVERAGEIFS($I$45:$AL$45, $I$123:$AL$123,">=" & I$120, $I$124:$AL$124, "<=" & I$121)

Use concatenate to make averageifs more flexible.

Cheers

Jan

This information is impressive..I am inspired with your post writing style & how continuously you describe this topic.

ReplyDeleteAnastasia date

This comment has been removed by the author.

ReplyDeleteI am so stuck. I'm trying to calculate the average monthly income over the previous 24 months at any given point in time. Every row of column H has a dollar amount representing a payment I received, and every row of column G has the date each check was received.

ReplyDeleteThe formula I wrote is AVERAGEIFS(H:H,G:G,">(MONTH(TODAY()))-24")

The error I get is #DIV/0!

Please help!

I am having a similar issue to Max S. I am trying to average riding times that have occurred on Saturdays only. My log includes dates for every day of the week. I have tried a couple of different formulas:

ReplyDelete=AVERAGE(IF($A4:$A39=WEEKDAY(7),L4:L39)) and

=AVERAGEIF(A:A,WORKDAY($A:A,1)=7,L:L)

I am basically stumped as to how to single out the Saturdays. Any help would be greatly appreciated.