How to Sum Numbers from part of a Cell

[marc.barton]

Hi, I am trying to work out how to sum a number from a cell that contains multiple bits of information. My information is like this

Game 1	Player A - 2, Player B - 1, Player C - 1
Game 2	Player A - 2, Player C - 2
Game 3	Player B - 2

I want to be able to sum the number after - for each player so that a get a total for a particular player i.e. Player A - Sum = 4.

Is this possible in excel? Appreciate any help anyone can give!

[FDibbins]

Hi, welcome to the forum

Where does this info come from? 1 of the biggest problems we see on the forum is members having "dirty" data to work with. If the data is is in a logical, structured format, summaries are simple. When values get mixed up with text, it makes things more complicated, and when multiple data (numeric and text) is included in 1 cell, it can become really difficult to extract.

I will see what I can come up with

[AlKey]

Deleted. I think I misread the requirements.

[FDibbins]

This is what I came up with, using helper columns for each player. These can be hidden if needed...

	A	B	C	D	E	F	G
1			Player A	Player B	Player C	Player D	Player E
2	Game 1	Player A - 2, Player B - 1, Player C - 0	2	1	0
3	Game 2	Player A - 4, Player C - 2	4	2
4	Game 3	Player B - 2	2
5	Game 4	Player B - 5, Player C - 4, Player D - 3, Player E - 2	5	4	3	2

C2=IF(COLUMNS($A$1:A$1)>LEN($B2)-LEN(SUBSTITUTE($B2,",",""))+1,"",IF(COLUMNS($A$1:A$1)<=LEN($B2)-LEN(SUBSTITUTE($B2,",","")),--MID($B2,FIND("xx",SUBSTITUTE($B2,",","xx",COLUMNS($A$1:A$1)),1)-1,1),--RIGHT($B2,1)))
copied down and across.

You would then just sum per player

edit: Nope, sorry, that doesnt work, back to the crawing board

[AlKey]

I like Ford's idea with the header so, here is my take on it

in C2 and pull formula to the right and down

=IFERROR(--MID(SUBSTITUTE($B2,","," "),SEARCH(C$1,SUBSTITUTE($B2,","," "))+11,2),0)

Row\Col	A	B	C	D	E	F
1	Game	Palyers	Player A	Player B	Player C	Player D
2	Game 1	Player A - 2, Player B - 1, Player C - 1	2	1	1	0
3	Game 2	Player A - 2, Player C - 2	2	0	2	0
4	Game 3	Player B - 2	0	2	0	0
5	Game 4	Player D - 10, Player C - 5	0	0	5	10
6			4	3	8	10

[FDibbins]

Thanks for that AlKey, I was heading into some deep stuff here....
=IF(COLUMNS($A$1:A$1)>LEN($B6)-LEN(SUBSTITUTE($B6,",",""))+1,"",IF(COLUMNS($A$1:A$1)<=LEN($B6)-LEN(SUBSTITUTE($B6,",","")),--MID($B6,FIND("xx",SUBSTITUTE($B6,",","xx",COLUMNS($A$1:A$1)),1)-1,1),--RIGHT($B6,1)))

Wasnt quite working, but your's is far simpler anyway

[marc.barton]

Thanks guys for your help. I have been looking at AlKey way of doing at that works great but for one issue. The data actually contains the Players name i.e. J.Bloggs, J.Smith, A.Nother. The length of the names can vary from 5 characters up to say 10. Is there a way of working it with variable lengths of data?

[FDibbins]

OK, there back to my v ersion...
C2=IFERROR(IF(COLUMNS($A$1:A$1)>LEN($B2)-LEN(SUBSTITUTE($B2,",",""))+1,"",IF(COLUMNS($A$1:A$1)<=LEN($B2)-LEN(SUBSTITUTE($B2,",","")),--MID(SUBSTITUTE(SUBSTITUTE($B2,C$1,"xx"),C$1,"xx"),FIND("xx",SUBSTITUTE(SUBSTITUTE($B2,C$1,"xx"),C$1,"xx"),1)+5,1),--RIGHT($B2,1))),"")
copied down and across

[AlKey]

This was rather easy to adjust my formula

=IFERROR(--MID(SUBSTITUTE($B2,","," "),SEARCH(C$1,SUBSTITUTE($B2,","," "))+LEN(C$1)+3,2),0)

Row\Col	A	B	C	D	E	F
1	Game	Players	J.Bloggs	J.Smith	A.Nother	M.Barton
2	Game 1	J.Bloggs - 2, J.Smith - 1, A.Nother - 17	2	1	17	0
3	Game 2	J.Bloggs - 2, A.Nother - 22	2	0	22	0
4	Game 3	J.Smith - 2	0	2	0	0
5	Game 4	M.Barton - 10, A.Nother - 5	0	0	5	10
6			4	3	44	10

[marc.barton]

Spot on! Just what I was after. Thanks to you both.

[AlKey]

Thank you for the feedback!

[FDibbins]

happy to help, thanks for the feedback