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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Aug 31, 2002
Messages
2
Dave,

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

Dougal
 

Forum statistics

Threads
1,144,213
Messages
5,723,055
Members
422,476
Latest member
beck85

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
Top