Creating an argument using text to give a numeric value

Dougal

New Member
Joined
Aug 31, 2002
Messages
2
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0

Forum statistics

Threads
1,218,749
Messages
6,144,276
Members
450,535
Latest member
Erlull

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top