Excel 2007 : AverageIfs for multiple dates returns #DIV/0!

Hey,

Id like to find an two month average for data, but I get a #DIV/0! error. Its fine when I try the averageif one month average, but not for more than one. Formula below:

=AVERAGEIFS(O:O,D:D, "Jul",D:D, "Aug")

Any resolutions for this?

Cheers.

Originally Posted by daddylonglegs

You can't use AVERAGEIFS like that, all conditions need to be satisfied but you effectively want an "OR". Try this version

=SUM(SUMIF(D:D,{"jul","aug"},O:O))/SUM(COUNTIF(D:D,{"jul","aug"}))

or an array formula

=AVERAGE(IF(D:D={"jul","aug"},O:O))

The latter needs to be confirmed with CTRL+SHIFT+ENTER

Thanks DLL, I've used the first choice, its solved the problem

Just out of interest, what would be the right scenario for an AVERAGEIFS function?

Originally Posted by daddylonglegs

You'd normally use AVERAGEIFS when you have more than one criteria in different columns, e.g. suppose you had a year in column E you might want to average for "jul" 2010 like this

=AVERAGEIFS(O:O,D:D,"Jul",E:E,2010)

Cheers for that.

I now have another issue, where I'd like to find a daily average. But I have data more than once on any given day. For examples sake, this works out as a daily average from two days:

=SUMIFS(T:T,D:D,{"Jan 10"},C:C,{"1"})+SUMIFS(T:T,D:D,{"Jan 10"},C:C,{"2"})/2

I've divided by 2 because I'm not sure how to formulate the COUNTIF function when there are multiple entries for a single date, is there a way of working round this?

It seems apparent to me, that to generate the daily average, I'd need to input a SUMIFS function 30-31 times for each month. I tried the formula:

=SUMIFS(T:T,D:D,{"Jan 10"},C:C,{"1"},C:C,{"2"})/2

But this formula above returned a value of 0.00.

Is there an easier way of doing this thus shortening the formula length?