# Creating an argument using text to give a numeric value

#### Dougal

##### New Member
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

Dave,

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

Dougal

Replies
1
Views
7K

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.

### Which adblocker are you using?

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

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