Thread: Average/IF function problem

Ok I'm stuck again. I've been using the Index/Match function and Average/IF function and not sure if I'm going to have to combine the two or what.

So I simplified my data to just show a few examples.

What I want to do is to select
month - cell D28
Department - cell E28
Indicator - cell A27

I want a formula that will then find the average for the
Month
Department
Indicator
Agent Hours (I want to be >50<250)

I also want to be able to change cells A28, A29 and A30 as I have a list. These headings are located in cells G1:L1.

So I have an IF formula in G27 (w/o the agent hours not sure how to include an IF for >50<250 for F2:F24), but I just put in H2:H24 for Accts/Hr.

=AVERAGE(IF(A2:A24=$D$28,IF(B2:B24=$E$28,IF(E2:E24=$A$27,H2:H24))))

Is there a way to replace H2:H24 so it reads what is in cell A29 and finds that in G1:L1 and uses the correct column.

Hope this makes sense and I appreciate any help.
thanks!