Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Cell multiple arguments | Excel Discussion (Misc queries) | |||
if/countif for 12 different arguments | Excel Worksheet Functions | |||
COUNTIF arguments | Excel Discussion (Misc queries) | |||
How do I set up an array using countif for 2 separate arguments. | Excel Worksheet Functions | |||
=COUNTIF How to if 2 or more arguments??? | Excel Worksheet Functions |