| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique counting
I have a spreadsheet a problem that is eluding me at the moment.
I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Data---Filter---Autofilter and then select 'Customs' from the drop down
menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
On Wed, 15 Nov 2006 12:39:02 -0800, E10
wrote: I wondered if there is formula for doing this? There is no `Customs` options available to me. I am using Excel 2003. Data---Filter---Autofilter and then select 'Customs' from the drop down menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Michael,
put the name of the target team in H2, then in H3 add the formula =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3: $F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F2,"") which is an array formula, so commit it with Ctrl-Shift-Enter, not just Enter. Copy H3 down to H40. This will list the name of the beaten team in column H in the row where they are the away team. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Michael" wrote in message ... On Wed, 15 Nov 2006 12:39:02 -0800, E10 wrote: I wondered if there is formula for doing this? There is no `Customs` options available to me. I am using Excel 2003. Data---Filter---Autofilter and then select 'Customs' from the drop down menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
If you apply data|Filter|autofilter, you can click on that arrow in the header
row cell and choose Custom from there. Although, I'm not sure if it would help. This will count the number of times Wigan was the Home team and outscored their opponents: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) And this will count the number of time wigan was the visiting team and won: =SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) So you can add these to find the number of wins--either use another cell or just combine the formulas into one cell: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) +SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Michael wrote: On Wed, 15 Nov 2006 12:39:02 -0800, E10 wrote: I wondered if there is formula for doing this? There is no `Customs` options available to me. I am using Excel 2003. Data---Filter---Autofilter and then select 'Customs' from the drop down menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Sorry, small typo
=IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3: $F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F3,"") that is F3 not F2 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Phillips" wrote in message ... Michael, put the name of the target team in H2, then in H3 add the formula =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3: $F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F2,"") which is an array formula, so commit it with Ctrl-Shift-Enter, not just Enter. Copy H3 down to H40. This will list the name of the beaten team in column H in the row where they are the away team. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Michael" wrote in message ... On Wed, 15 Nov 2006 12:39:02 -0800, E10 wrote: I wondered if there is formula for doing this? There is no `Customs` options available to me. I am using Excel 2003. Data---Filter---Autofilter and then select 'Customs' from the drop down menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Dave,
He wants to know how many times Wigan do the double, not just how many times they win. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Dave Peterson" wrote in message ... If you apply data|Filter|autofilter, you can click on that arrow in the header row cell and choose Custom from there. Although, I'm not sure if it would help. This will count the number of times Wigan was the Home team and outscored their opponents: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) And this will count the number of time wigan was the visiting team and won: =SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) So you can add these to find the number of wins--either use another cell or just combine the formulas into one cell: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) +SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Michael wrote: On Wed, 15 Nov 2006 12:39:02 -0800, E10 wrote: I wondered if there is formula for doing this? There is no `Customs` options available to me. I am using Excel 2003. Data---Filter---Autofilter and then select 'Customs' from the drop down menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Ewwww!
How about: =SUMPRODUCT(--(d2:d100="wigan"),--(f2:f100="Sunderland"),--(e2:e100g2:g100)) To get the times wigan was the home team and sunderland was the visitor. And similarly for when wigan was the visitor. Bob Phillips wrote: Dave, He wants to know how many times Wigan do the double, not just how many times they win. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Dave Peterson" wrote in message ... If you apply data|Filter|autofilter, you can click on that arrow in the header row cell and choose Custom from there. Although, I'm not sure if it would help. This will count the number of times Wigan was the Home team and outscored their opponents: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) And this will count the number of time wigan was the visiting team and won: =SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) So you can add these to find the number of wins--either use another cell or just combine the formulas into one cell: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) +SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Michael wrote: On Wed, 15 Nov 2006 12:39:02 -0800, E10 wrote: I wondered if there is formula for doing this? There is no `Customs` options available to me. I am using Excel 2003. Data---Filter---Autofilter and then select 'Customs' from the drop down menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
That gives you 1 for Wigan v Sunderland, but that will also give you a 1 for
Wigan v Bolton, but the away leg was a 1-1 draw, so it was not a double for Wigan. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Dave Peterson" wrote in message ... Ewwww! How about: =SUMPRODUCT(--(d2:d100="wigan"),--(f2:f100="Sunderland"),--(e2:e100g2:g100) ) To get the times wigan was the home team and sunderland was the visitor. And similarly for when wigan was the visitor. Bob Phillips wrote: Dave, He wants to know how many times Wigan do the double, not just how many times they win. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Dave Peterson" wrote in message ... If you apply data|Filter|autofilter, you can click on that arrow in the header row cell and choose Custom from there. Although, I'm not sure if it would help. This will count the number of times Wigan was the Home team and outscored their opponents: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) And this will count the number of time wigan was the visiting team and won: =SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) So you can add these to find the number of wins--either use another cell or just combine the formulas into one cell: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) +SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Michael wrote: On Wed, 15 Nov 2006 12:39:02 -0800, E10 wrote: I wondered if there is formula for doing this? There is no `Customs` options available to me. I am using Excel 2003. Data---Filter---Autofilter and then select 'Customs' from the drop down menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Ohhhhhh.
How many times did Wigan beat anyone both home and away.... I guess I'd do each individually and instead of adding the home and way totals, I'd multiply them: =(SUMPRODUCT(--(d2:d100="wigan"), --(f2:f100="Sunderland"), --(e2:e100g2:g100))0) *(SUMPRODUCT(--(f2:f100="wigan"), --(df2:d100="Sunderland"), --(e2:e100<g2:g100))0) Do this for each pair of teams and then add them up. Do you have a fancier formula that works? Bob Phillips wrote: That gives you 1 for Wigan v Sunderland, but that will also give you a 1 for Wigan v Bolton, but the away leg was a 1-1 draw, so it was not a double for Wigan. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Dave Peterson" wrote in message ... Ewwww! How about: =SUMPRODUCT(--(d2:d100="wigan"),--(f2:f100="Sunderland"),--(e2:e100g2:g100) ) To get the times wigan was the home team and sunderland was the visitor. And similarly for when wigan was the visitor. Bob Phillips wrote: Dave, He wants to know how many times Wigan do the double, not just how many times they win. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Dave Peterson" wrote in message ... If you apply data|Filter|autofilter, you can click on that arrow in the header row cell and choose Custom from there. Although, I'm not sure if it would help. This will count the number of times Wigan was the Home team and outscored their opponents: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) And this will count the number of time wigan was the visiting team and won: =SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) So you can add these to find the number of wins--either use another cell or just combine the formulas into one cell: =SUMPRODUCT(--(d2:d100="wigan"),--(e2:e100g2:g100)) +SUMPRODUCT(--(f2:f100="wigan"),--(e2:e100<g2:g100)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Michael wrote: On Wed, 15 Nov 2006 12:39:02 -0800, E10 wrote: I wondered if there is formula for doing this? There is no `Customs` options available to me. I am using Excel 2003. Data---Filter---Autofilter and then select 'Customs' from the drop down menus. Then use "Michael" wrote: I have a spreadsheet a problem that is eluding me at the moment. I have 4 columns which consists of 1 header row and 38 data rows. This a is a simply to find out how many teams has in this case Wigan beaten the opposition home and away. The example below shows Wigan has beaten 3 teams both home and away Sunderland, Aston Villa and Man City. Col D Col E Col F Col G Home Team HS Away Team AS Wigan 0 Chelsea 1 Charlton 1 Wigan 0 Man Utd 4 Wigan 0 Wigan 1 Sunderland 0 West Brom 1 Wigan 2 Wigan 1 Middlesbrough 1 Everton 0 Wigan 1 Wigan 2 Bolton 1 Wigan 1 Newcastle 0 Aston Villa 0 Wigan 2 Wigan 1 Fulham 0 Portsmouth 0 Wigan 2 Wigan 2 Arsenal 3 Wigan 1 Tottenham 2 Liverpool 3 Wigan 0 Chelsea 1 Wigan 0 Wigan 3 Charlton 0 Wigan 4 Man City 3 West Ham 0 Wigan 2 Wigan 0 Blackburn 3 Birmingham 2 Wigan 0 Wigan 0 West Brom 1 Middlesbrough 2 Wigan 3 Wigan 1 Everton 1 Bolton 1 Wigan 1 Wigan 0 Liverpool 1 Tottenham 2 Wigan 2 Wigan 1 Man Utd 2 Sunderland 0 Wigan 1 Man City 0 Wigan 1 Wigan 1 West Ham 2 Blackburn 0 Wigan 0 Wigan 1 Birmingham 1 Newcastle 3 Wigan 1 Wigan 3 Aston Villa 2 Fulham 1 Wigan 0 Wigan 1 Portsmouth 2 Arsenal 4 Wigan 2 Data is in the range D3:G40 Col D & Col F are text. Col E & Col G are numerical. Can you help? thanks Micah -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
"Dave Peterson" wrote in message ... Ohhhhhh. How many times did Wigan beat anyone both home and away.... 3 times I guess I'd do each individually and instead of adding the home and way totals, I'd multiply them: =(SUMPRODUCT(--(d2:d100="wigan"), --(f2:f100="Sunderland"), --(e2:e100g2:g100))0) *(SUMPRODUCT(--(f2:f100="wigan"), --(df2:d100="Sunderland"), --(e2:e100<g2:g100))0) Do this for each pair of teams and then add them up. Lot of formulae <G! Do you have a fancier formula that works? Not fancy, I posted this just before you joined ... put the name of the target team in H2, then in H3 add the formula =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3: $F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F2,"") which is an array formula, so commit it with Ctrl-Shift-Enter, not just Enter. Copy H3 down to H40. This will list the name of the beaten team in column H in the row where they are the away team. This lists all the teams that the double is done over, so a simple COUNTA gets the count. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Your original post wasn't there when I retrieved the headers--else I would have
stayed out of it! Bob Phillips wrote: "Dave Peterson" wrote in message ... Ohhhhhh. How many times did Wigan beat anyone both home and away.... 3 times I guess I'd do each individually and instead of adding the home and way totals, I'd multiply them: =(SUMPRODUCT(--(d2:d100="wigan"), --(f2:f100="Sunderland"), --(e2:e100g2:g100))0) *(SUMPRODUCT(--(f2:f100="wigan"), --(df2:d100="Sunderland"), --(e2:e100<g2:g100))0) Do this for each pair of teams and then add them up. Lot of formulae <G! Do you have a fancier formula that works? Not fancy, I posted this just before you joined ... put the name of the target team in H2, then in H3 add the formula =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3: $F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F2,"") which is an array formula, so commit it with Ctrl-Shift-Enter, not just Enter. Copy H3 down to H40. This will list the name of the beaten team in column H in the row where they are the away team. This lists all the teams that the double is done over, so a simple COUNTA gets the count. -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
I sometimes find that posts aren't there, and only know because I see the
reply. Can you see it now? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Dave Peterson" wrote in message ... Your original post wasn't there when I retrieved the headers--else I would have stayed out of it! Bob Phillips wrote: "Dave Peterson" wrote in message ... Ohhhhhh. How many times did Wigan beat anyone both home and away.... 3 times I guess I'd do each individually and instead of adding the home and way totals, I'd multiply them: =(SUMPRODUCT(--(d2:d100="wigan"), --(f2:f100="Sunderland"), --(e2:e100g2:g100))0) *(SUMPRODUCT(--(f2:f100="wigan"), --(df2:d100="Sunderland"), --(e2:e100<g2:g100))0) Do this for each pair of teams and then add them up. Lot of formulae <G! Do you have a fancier formula that works? Not fancy, I posted this just before you joined ... put the name of the target team in H2, then in H3 add the formula =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3: $F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F2,"") which is an array formula, so commit it with Ctrl-Shift-Enter, not just Enter. Copy H3 down to H40. This will list the name of the beaten team in column H in the row where they are the away team. This lists all the teams that the double is done over, so a simple COUNTA gets the count. -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
On Wed, 15 Nov 2006 22:47:38 -0000, "Bob Phillips"
wrote: Sorry, small typo =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3 :$F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F3,"") that is F3 not F2 Thanks Bob, Dave and E10 for your solutions. The above worked at treat. I tried to something similar to the above formula but was using the sumproduct function. I have edited this formula to obtain double losses and double draws. The later required 2 helper columns. |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Double losses/draws? Do you mean where they had the double done on them, and
where both games were draws? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Michael" wrote in message ... On Wed, 15 Nov 2006 22:47:38 -0000, "Bob Phillips" wrote: Sorry, small typo =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3 :$F$40=D3)*($E$3:$E$40<$G$ 3 :$G$40),1))),F3,"") that is F3 not F2 Thanks Bob, Dave and E10 for your solutions. The above worked at treat. I tried to something similar to the above formula but was using the sumproduct function. I have edited this formula to obtain double losses and double draws. The later required 2 helper columns. |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Yep. It was just that I grabbed the headers before you posted. A timing
issue--nothing more. Bob Phillips wrote: I sometimes find that posts aren't there, and only know because I see the reply. Can you see it now? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Dave Peterson" wrote in message ... Your original post wasn't there when I retrieved the headers--else I would have stayed out of it! Bob Phillips wrote: "Dave Peterson" wrote in message ... Ohhhhhh. How many times did Wigan beat anyone both home and away.... 3 times I guess I'd do each individually and instead of adding the home and way totals, I'd multiply them: =(SUMPRODUCT(--(d2:d100="wigan"), --(f2:f100="Sunderland"), --(e2:e100g2:g100))0) *(SUMPRODUCT(--(f2:f100="wigan"), --(df2:d100="Sunderland"), --(e2:e100<g2:g100))0) Do this for each pair of teams and then add them up. Lot of formulae <G! Do you have a fancier formula that works? Not fancy, I posted this just before you joined ... put the name of the target team in H2, then in H3 add the formula =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3: $F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F2,"") which is an array formula, so commit it with Ctrl-Shift-Enter, not just Enter. Copy H3 down to H40. This will list the name of the beaten team in column H in the row where they are the away team. This lists all the teams that the double is done over, so a simple COUNTA gets the count. -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
On Thu, 16 Nov 2006 18:31:32 -0000, "Bob Phillips"
wrote: Double losses/draws? Do you mean where they had the double done on them, and where both games were draws? As the formula you posted calculated the home and away wins over a team(s), I was just curious to see the team we could not beat home or away, that's all. |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
I just had a go at the double draws, and just needed the one helper column.
What did you use in 2? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Michael" wrote in message ... On Thu, 16 Nov 2006 18:31:32 -0000, "Bob Phillips" wrote: Double losses/draws? Do you mean where they had the double done on them, and where both games were draws? As the formula you posted calculated the home and away wins over a team(s), I was just curious to see the team we could not beat home or away, that's all. |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
On Wed, 15 Nov 2006 22:34:22 -0000, "Bob Phillips"
wrote: Michael, put the name of the target team in H2, then in H3 add the formula =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3 :$F$40=D3)*($E$3:$E$40<$G$3 :$G$40),1))),F2,"") which is an array formula, so commit it with Ctrl-Shift-Enter, not just Enter. Copy H3 down to H40. This will list the name of the beaten team in column H in the row where they are the away team. I am stuck again, I list of teams which are spread unequally H3:H40. I trying to list the names in alternate rows in column M. Can you help again? Michael. |
|
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting
Michael,
Assuming that you still list the names in H3:H40, then add this to M3 =IF(ISERROR(SMALL(IF($H$3:$H$40<"",ROW($H$3:$H$40 )-MIN(ROW($H$3:$H$40))+1," "),ROW($A1:$A40))),"", INDEX(H$3:H$40,SMALL(IF($H$3:$H$40<"",ROW($H$3:$H $40)-MIN(ROW($H$3:$H$40))+ 1,""),ROW($A1:$A40)))) again an array formula, and copy down. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Michael" wrote in message ... On Wed, 15 Nov 2006 22:34:22 -0000, "Bob Phillips" wrote: Michael, put the name of the target team in H2, then in H3 add the formula =IF(AND(D3=H$2,E3G3,MAX(IF(($D$3:$D$40=F3)*($F$3 :$F$40=D3)*($E$3:$E$40<$G$ 3 :$G$40),1))),F2,"") which is an array formula, so commit it with Ctrl-Shift-Enter, not just Enter. Copy H3 down to H40. This will list the name of the beaten team in column H in the row where they are the away team. I am stuck again, I list of teams which are spread unequally H3:H40. I trying to list the names in alternate rows in column M. Can you help again? Michael. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting unique values | Excel Discussion (Misc queries) | |||
| Counting Unique Values | Excel Worksheet Functions | |||
| Counting unique records with additional criteria | Excel Worksheet Functions | |||
| Counting Unique Cells When Spread Sheet is Filtered | Excel Worksheet Functions | |||
| Counting Unique Records with multiple conditions | Excel Worksheet Functions | |||
Linear Mode
