AverageIFS

[micha3l87]

Hey everyone,

I'm trying to create a table for response times by department and month. All the data is coming from a different sheet where it's all stored.
I did a tiny version below to attempt best explain what I've been doing.
I'm basically a little better than a beginner with excel, but I've managed to work out a formula

=AVERAGEIFS(Year!Y2:Y1352,Year!AB2:AB1352,">="&F5,Year!AB2:AB1352,"<="&EOMONTH(F5,0))

I can't seem to figure out, how to get excel to check department, then return with the average response time.

In a simpler sheet, I was using this formula
=IFERROR(AVERAGEIF(A2:A1352,"ITDepartment",Y2:Y1352),"")

Any help would be greatly appreciated!

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]3[/TD]
[TD](F)[/TD]
[TD](G) [/TD]
[TD](H)[/TD]
[TD](I)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]MONTHS[/TD]
[TD]DEPARTMENT 1[/TD]
[TD]DEPARTMENT 2[/TD]
[TD]Department 3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]January[/TD]
[TD]Response Time[/TD]
[TD]Response Time[/TD]
[TD]Response Time[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]February[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]March[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]April[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]May[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]June[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]August[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]September[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]October[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]November[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]December[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Hi, welcome to the board

1. Your formula references don't match your sample?
2. do the cells under Months contains text months (January, February etc) or real dates (1/1/2017, 2/1/2017 etc)?
3. I don't see anything in your formula that would check Dept?

=AVERAGEIFS(Year!Y2:Y1352(data to average?),Year!AB2:AB1352 (date-range?),">="&F5,Year!AB2:AB1352 (date-range?),"<="&EOMONTH(F5,0))

 

[barry houdini]

Hello micha3l87, welcome to MrExcel

I think you should be able to add the department criterion to your existing AVERAGEIFS formula, e.g. something like this:

=AVERAGEIFS(Year!Y2:Y1352,Year!AB2:AB1352,">="&F5,Year!AB2:AB1352,"<="&EOMONTH(F5,0),Year!A2:A1352,"ITDepartment")

 

[micha3l87]

Thank you so much!
Looking at it now, it seems so simple!
That's been a major road block for me today!