Creating an argument using text to give a numeric value

[Dougal]

I am a Referee in Chief for hockey and to save time, I need to find a formula for the following.
Column A1:A200 has the type of league ie Novice
Column B1:B200 has the referee assigned ie Dougal
Column C1:C200 + D1:D200 has the (2) linesman assigned. ie Dougal Bob
On a seperate page I need to now how many games each official did per division (aprox 20 divisions)

I have tried a bunch of different functions all to no avail.

If any one has a solution I would appreciate it.

Thanks,
Dougal

 

[Dave Patton]

On 2002-09-01 08:55, Dougal wrote:
I am a Referee in Chief for hockey and to save time, I need to find a formula for the following.
Column A1:A200 has the type of league ie Novice
Column B1:B200 has the referee assigned ie Dougal
Column C1:C200 + D1:D200 has the (2) linesman assigned. ie Dougal Bob
On a seperate page I need to now how many games each official did per division (aprox 20 divisions)

I have tried a bunch of different functions all to no avail.

If any one has a solution I would appreciate it.

Thanks,
Dougal

A. You may want to look at Pivot Tables

B. Formula suggestion

1. What column shows the division?

2. I will Assume Column F shows division.

3. I will assume a person can work as Referee or Linesman.

4. The Formula has no error checking. If a person is shown 2 times for a game he will be counted twice.

5. Name Criteria is in Column A and Division criteria in Column B.

6. Source information is on Sheet named Data.


=SUMPRODUCT((Data!$E$2:$E$6=B2)*((Data!$B$2:$B$6=A2)+(Data!$C$2:$C$6=A2)+(Data!$D$2:$D$6=A2)))

Revise the Sheet name and data ranges as appropriate.

Copy the formula down or Fill Down.

HTH Dave
This message was edited by Dave Patton on 2002-09-01 09:24

 

[Dougal]

Dave,

Thank-you it works great. Thank-you for taking the time to reply.

Dougal