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