this is frying my brian!!!! Help

[JD1]

Can anyone help me - i'm new to this, but have been trying to solve a problem which i think may have beaten me!!!

Can anyone give me any ideas on a formula that would create a total across 2 columns in 2 sets of criteria match!!
ie...
Column A Column B

1 Jason Won
2 Fred Won
3 Jim Lost
4 Jason Lost
5 Mary Won
6 Fred Lost
7 Jason Won

On a Summary sheet i want to creat a sheet that will tell me how many times Jason has won and how many Jason has lost
ie...

Name Won Lost

Jason 2 1
Fred 1 1
Jim 0 1
Mary 0 1

Can anyone Help me???????? thank you so much if you can

Jason

 

[Richard Schollar]

Hi Jason

Here's one way - I would strongly recommend investigating Pivot Tables, however, as they were ctreated for just this kind of analysis:

Excel Workbook
	A	B	C	D	E	F
1	Jason	Won			Won	Lost
2	Fred	Won		Jason	2	1
3	Jim	Lost		Fred	1	1
4	Jason	Lost		Jim	0	1
5	Mary	Won		Jason	2	1
6	Fred	Lost		Mary	1	0
7	Jason	Won
Sheet1

 

[Beate Schmitz]

Hello,

Excel Workbook
	A	B	C	D	E	F
1	Jason	Won		4
2	Fred	Won		#Distinct List#	Won	Lost
3	Jim	Lost		Jason	2	1
4	Jason	Lost		Fred	1	1
5	Mary	Won		Jim	0	1
6	Fred	Lost		Mary	1	0
7	Jason	Won
Sheet

The formulas of row 3 can be copied down.

 

[JD1]

Hi there and many thanks for the reply - but this doesn't look right - is it and am i just being stupid - but there is miles of text to this formula?? relating to font; colour; text etc!!!

 

[Richard Schollar]

In what way does it not look right? What Beate has given you is a formula to additionally extract the unique names from the A column (whereas I just used Advanced Filter to produce the same). The actual count of the wins/losses is functionally identical between our two posts.

 

[goldenvision]

Isn't Brian a bit annoyed at being in a frying pan!!!!

 

[Beate Schmitz]

Hi Richard,

I like advanced filter too. But how comes the mistake in your solution having Jason twice?

 

[Richard Schollar]

Whoops - guess that would be because I should have created some headings!

Completely missed that