count if?

bigblue40

Board Regular
Joined
Aug 3, 2003
Messages
102
Hi, I have the following data in a number of cells and I would like to perform a count on match if a team has won, drawn or lost, suggestions appreciated on how to count on these possible outcomes


Nottinghamshire beat Derbyshire by by 53 runs
Lancashire beat Yorkshire by 30 runs
Durham beat Leicestershire by 8 wickets
Midlands/West/Wales
Worcestershire v Warwickshire - No result (Match abandoned)
Gloucestershire beat Somerset by 8 wickets
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
not entirely sure I follow ? are you saying just specify the result type or are you looking to identify which team won etc... with the data layout you have it will be tricky (presumably first team is home team) ?

I believe the only constant you'll have is that Essex won

;)
 
Upvote 0
yes that is what I wanted to do, downloaded the data from a web query and wanted to work out who won lost and drew,

I know its normally Kent.
 
Upvote 0
Not tonight they didn't...

You didn't answer the issue about whether first team was home team or if by coincidence all the home teams you listed either did not play or won... point is if you can be sure the first side would be the winner (if text says "Beat") then it makes your life a little easier.

P.S. I've played against Graham Napier (albeit when he was about 14) and he was good then.. he'll give your boys one helluva a shoeing!
 
Upvote 0
well it rained tonight, I once bowled the great Sir Colin Cowdrey, lunch at Canterbury and all the kids on the grounds!
All the data is in the format of team a beat team B, unless if course its a no result or tie.

PS

I've got a job and not sure I want it any more!
 
Upvote 0
Sir Colin Cowdrey - ok that officially gazumps my Graham Napier... :)

The below would extract winner and ignore other entries for now... assumes web download in column A

=IF(ISERROR(SEARCH(" beat ",$A1)),"",LEFT($A1,SEARCH(" beat ",$A1)-1))

Still curious to the layout do you want, ie if web download is in A, do you want winners in B, losers in C ? if draw what do you want to do ? If N/R what do you want to do ?

P.S. Not having a job on days like today is no bad thing... day 1 Eng / SA... will you be watching... I know I will...
(cue copious amounts of rain...)
 
Last edited:
Upvote 0
As I said, Ive got a job hense late reply, Ive got a spare for Lords tomorrow if you fancy it! Its gonna rain all day though!
Basically Ive sucked down all the 20/20 results for 2007/2006 and wanted to work out all the teams performances. Your formula does the trick and works out who has won, how do I work out who has lost. I have removed the $ in your formula so I can run on several cells.
=IF(ISERROR(SEARCH(" beat ",$A12)),"",LEFT($A12,SEARCH(" beat ",$A12)-1))

KEnt won again!
 
Upvote 0
You could use the below to get the team that lost (eg Kent)

This is for C12, assuming A12 = result, B12 = formula for victor (eg Essex)

=IF($B12="","",TRIM(MID(SUBSTITUTE($A12," ",REPT(" ",100)),200,100)))

This all assumes of course the string of "Essex beat Kent" rather than the "Essex Eagles beat Kent Spitfires"

HTH
 
Upvote 0
fantastic!!! just the job(no pun intended) With a bit of editing I have replaced "beat" with "no result" and got them as well.

many thanks, I will let you know how many times Essex lost!
 
Upvote 0
Kent did well in 2007!
P W L T NR
Kent 11 7 2 1 1
Gloucestershire 11 6 3 0 2
Sussex 10 6 3 0 1
Lancashire 11 5 2 0 4
Warwickshire 9 5 3 0 1
Nottinghamshire 9 4 2 0 3
Surrey 8 4 4 0 0
Essex 8 3 4 0 1
Somerset 8 3 5 0 0
Worcestershire 9 3 3 0 3
Yorkshire 8 3 4 0 1
Leicestershire 8 2 1 0 5
Middlesex 8 2 3 0 3
Northamptonshire 8 2 3 0 3
Durham 8 1 4 0 3
Glamorgan 8 1 4 0 3
Hampshire 8 1 4 1 2
Derbyshire 8 0 4 0 4
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top