Working with 2 arguments over 4 columns (countif ??)
 

I am trying to determine a formula for this
DATA
A B C D
1 Team 1 Team 2 Ump 1 Ump2
2 Hawks Districts Bert Frog
3 Liston Wirrulla Shag Hassy
4 United Centrals Bucky Gus
5 Hawks Centrals Bucky Frog
6 Liston United Hass Shag
7 Districts Wirrulla Gus Bert

CALCULATION Times that Umpire has umpired a specific club
8 Wirrulla Centrals United Liston
9 Shag 1 0 1 2
10 Bert 1 0 0 0

My problem is working the calculation in B9 / C9 / B10 / C10 etc

So for example I want to know how many times Shag (who could be named
anywhere in the range C2:D7) has umpired Liston (who could be named anywhere
in the range A2:B7)

Working with 2 arguments over 4 columns (countif ??)
 

Try this entered in B9:

=SUMPRODUCT(($C$2:$C$7=$A9)+($D$2:$D$7=$A9),($A$2: $A$7=B$8)+($B$2:$B$7=B$8))

Copy across to E9 then down to B10:E10.

--
Biff
Microsoft Excel MVP


"Dubbo" wrote in message
...
I am trying to determine a formula for this
DATA
A B C D
1 Team 1 Team 2 Ump 1 Ump2
2 Hawks Districts Bert Frog
3 Liston Wirrulla Shag Hassy
4 United Centrals Bucky Gus
5 Hawks Centrals Bucky Frog
6 Liston United Hass Shag
7 Districts Wirrulla Gus Bert

CALCULATION Times that Umpire has umpired a specific club
8 Wirrulla Centrals United Liston
9 Shag 1 0 1
2
10 Bert 1 0 0
0

My problem is working the calculation in B9 / C9 / B10 / C10 etc

So for example I want to know how many times Shag (who could be named
anywhere in the range C2:D7) has umpired Liston (who could be named
anywhere
in the range A2:B7)

Working with 2 arguments over 4 columns (countif ??)
 

Hi,

Here is another solution:

=SUMPRODUCT(($C$2:$D$7=$A9)*(($A$2:$A$7=B$8)+($B$2 :$B$7=B$8)))


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Dubbo" wrote:

I am trying to determine a formula for this
DATA
A B C D
1 Team 1 Team 2 Ump 1 Ump2
2 Hawks Districts Bert Frog
3 Liston Wirrulla Shag Hassy
4 United Centrals Bucky Gus
5 Hawks Centrals Bucky Frog
6 Liston United Hass Shag
7 Districts Wirrulla Gus Bert

CALCULATION Times that Umpire has umpired a specific club
8 Wirrulla Centrals United Liston
9 Shag 1 0 1 2
10 Bert 1 0 0 0

My problem is working the calculation in B9 / C9 / B10 / C10 etc

So for example I want to know how many times Shag (who could be named
anywhere in the range C2:D7) has umpired Liston (who could be named anywhere
in the range A2:B7)