Using DAY() function with AVERAGEIFS()

[djmsenior]

I've got an AVERAGEIFS() formula that kind of works great but I need to add the DAY() converter in to save having another converter column.

Scenario attached.

If I use this formula - =AVERAGEIFS($C$2:$C$11,$A$2:$A$11,"="&G$3,$B$2:$B$11,"="&$E5) it works.

But if I use this formula - =AVERAGEIFS($C$2:$C$11,$A$2:$A$11,"="&G$3,day($B$2:$B$11),"="&$F4)

Excel shows me an error.

What am I doing wrong?

 

[Fluff]

How about

=AVERAGE(filter($C$2:$C$11,($A$2:$A$11=G$3)*(day($B$2:$B$11)=$F4)))

 

[djmsenior]

How about

=AVERAGE(filter($C$2:$C$11,($A$2:$A$11=G$3)*(day($B$2:$B$11)=$F4)))

Fluff - you've done it again! Many, many thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[djmsenior]

Fluff, I may have spoken too soon...

I'm putting the formula into my working sheet but it's coming up with a DIV/0 error.

Your assistance gratefully received.

 

[Fluff]

Can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.