If the data above is on Sheet1 and the Summary is on Sheet 2 th eformulas
below will work.. On sheet 2 put in column A and b each unique combination
of Time and Route ID

Sheet 2
A B C D
Time Route ID Total Call Volume Average

0800 a
0800 b
0815 a
0815 b


Sheet1!A1:A100 is the size of the TIME and A1 is the time on sheet2.
Sheet1!B1:B100 is the size of the Route ID and B1 is the Route ID on sheet2.


Call volume in sheet2 column C
=sumproduct(--(Sheet1!A$1:A$100=A1),--(Sheet1!B$1:B$100=B1),Sheet1!C$1:C$100)

Average Call Handling in column D on sheet2
=(sumproduct(--(Sheet1!A$1:A$100=A1),--(Sheet1!B$1:B$100=B1),Sheet1!L$1:L$100) +

sumproduct(--(Sheet1!A$1:A$100=A1),--(Sheet1!B$1:B$100=B1),Sheet1!N$1:N$100)/C1

"Zakynthos" wrote:

I would like to use a formula (LOOKUP?) to enable me to do the following:

In column A: I have the time in 15 minute time intervals
In column B: I have variable groups called €˜route IDs (€˜a, €˜b, ;c; etc)
In column C: I have call volumes per 15 minute period for each route ID
In columns L & N: I have 2 sets of data which when summed will give me
the call handling time for that 15 min period for that route ID.

The info is organised as follows
A B C €¦€¦€¦. L N

Time Route ID Call volume CHT1 CHT2

08:00 a 10 778 521
0800 a 15 498 615
0800 b 14 769 1021
0800 a 17 28 1400
0800 b 25 269 743
0800 a 18 890 1005
0815 b 21 777 650
0815 a 45 900 740
0815 a 46 210 367
0815 b 74 456 789

etc

What formula/function would I need and how would this be applied to the
above to give me:

1. The average call volume for each 15 minute period for a particular route
ID (say called €˜a, €˜b, €˜c etc)

2. The average call handling time (CHT) for each 15 minute period for a
particular route ID

Many thanks