complex average if criteria matches

[rbekka33]

I need to total the number of hours spent on a particular activity. Th
challenge is that each activity is listed individually and some ar
group activities.

What i would like to do is test cell a2 against cell a3 and if the
match, then count those cells and divide by the total number of hour
in the corresponding Time column - L2 and L3.

If a2, a3, a4 and a5 match I basically need to do the same thing. Onc
the immediate cell below no longer matches then the formula should mov
on.

I have tried conditional sum, pivot tables, and subtotalling etc an
none give me the needed answer.

Course
Number Total in Hours Date
1 1 07/08/2004
2 2 07/09/2004
3 1 07/09/2004
4 1 07/22/2004
5 3 08/02/2004
6 2 08/02/2004
7 3.5 08/03/2004
8 0.5 08/04/2004
9 2.5 08/06/2004
10 2 08/11/2004
10 2 08/11/2004
10 2 08/11/2004
10 2 08/11/2004
10 2 08/11/2004
10 2 08/11/2004
10 2 08/11/2004
10 2 08/11/2004
11 2 08/12/2004
11 2 08/12/2004
11 2 08/12/2004
11 2 08/12/2004
11 2 08/12/2004
12 3 08/13/2004
13 1.5 08/17/2004
14 0.5 08/17/2004
15 2 08/17/2004
15 2 08/17/2004
15 2 08/17/2004
15 2 08/17/2004

thank

 

[Helen Trim]

This function will give you the required results:

=IF(A2=A3,"",SUMIF(A:A,A2,B:B)/COUNTIF(A:A,A2))

HTH
Helen

 

[rbekka33]

works a treat

thanks!!!