Unique counting

[Michael]

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

 

[Guest]

Data--->Filter--->Autofilter and then select 'Customs' from the drop down
menus.

Then use

 

[Michael]

On Wed, 15 Nov 2006 12:39:02 -0800, E10

I wondered if there is formula for doing this?

There is no `Customs` options available to me.
I am using Excel 2003.

 

[Bob Phillips]

Michael,

put the name of the target team in H2, then in H3 add the formula

=IF(AND(D3=H$2,E3>G3,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)

 

[Dave Peterson]

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:e100>g2: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:e100>g2: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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Bob Phillips]

Sorry, small typo

=IF(AND(D3=H$2,E3>G3,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]

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]

Ewwww!

How about:

=SUMPRODUCT(--(d2:d100="wigan"),--(f2:f100="Sunderland"),--(e2:e100>g2: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]

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]

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:e100>g2: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?

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)

 

[Bob Phillips]

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:e100>g2: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?

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,E3>G3,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]

Your original post wasn't there when I retrieved the headers--else I would have
stayed out of it!

 

[Bob Phillips]

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)

 

[Michael]

Sorry, small typo

=IF(AND(D3=H$2,E3>G3,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.

 

[Bob Phillips]

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)

 

[Dave Peterson]

Yep. It was just that I grabbed the headers before you posted. A timing
issue--nothing more.

 

[Michael]

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.

 

[Bob Phillips]

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]

Michael,

put the name of the target team in H2, then in H3 add the formula

=IF(AND(D3=H$2,E3>G3,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.

 

[Bob Phillips]

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)