I have a worksheet that will look as follows:


A B C D E
1 Region Title Amount
2 A Senior 25,000
3 A Senior 30,000
4 B Trainee 17,000
5 B Trainee 22,000
6 A Agent 11,000
7 A Agent 14,000
8 A Trainee 17,000
9 C Senior 25,000
10 C Trainee 14,000
11 B Senior 32,000
12
13
14
15
16
17 Region
18 A B C
19 Title
20 Agent 12,500
21 Senior 27,500 32,000 25,000
22 Trainee 17,000 19,500 14,000


Cells A2 through C11 will contain information about an employee's region,
their title and the salary.


I want to write a formula tht will calculate the average salary for a given
title for a given region. Cells B20 to D22 contain the desired output.

Because of how I am linking the results in Cells B20 to D22 and the fact
that I will be manipulating the salary information, I do not want to use a
pivot table. Instead I want to write a formula that will in essence produce
what a pivot table would produce.

I thnink the formula that should go in cell B20 is as follows:

=IF(AND(A2:A11="A",B2:B11="Agent"),AVERAGE(C2:C11) )

When I use this formula I get the #VALUE error message.

Any thoughts?