Help .... COUNTIF to count the same word in a cell twice?

[peterh1967a]

Hi, hoping someone can help.

I’ve created a spreadsheet to track the results of my Sunday league soccer side. It contains cells which record each fixture - the date, opponents, whether it was home or away, the home score and away score and the scorers. I have one cell in which I record the scorers – so this can contain a few different players names if we score more than once. So for example if we score 3 goals and one player scores twice I record this as “Smith, Smith, Jones”

I’ve created different sheets for different seasons. I now want to create a sheet that tallies the number of goals scored by each player – which links to the season sheet.

I’ve got a COUNTIF formula that counts the number of times each players name appears in the ‘scorer cell’. That formula is ….. =COUNTIFS('Season 1'!$O$12:$O$60,"*"&E12&"*") …. Where 'Season 1'!$O$12:$O$60 is the sheet and cells with the scorers in it and E12 is the name of each player on the ‘scorer’ sheet.

It works fine so long as each player only scores once in each game, but if a player scores twice it only recognizes that the player has scored once. So in the example above (Smith, Smith, Jones) it recognizes that Smith scored 1 goal and Jones 1 goal.

So, hoping that someone can tell me how to count accurately when I have two identical names in the cell.

Would point out that I’m no excel aficionado by any means (as you can probably tell by the question) and that my spreadsheet may not be the most elegant way of recording the information I need. I don’t understand macros etc, but would appreciate any advice.

[Ron Coderre]

Using this example:

	A	B	C	D	E
1	Scorers			Name	Scores
2	Smith, Smith, Jones			Smith
3	Smith, Smith, Jones			Jones
4	Smith, Smith, Jones			Smithfield
5	Smithfield, Smith

This formula, copied down, returns the number of scores for the referenced player

Please Login or Register  to view this content.

In that example, these are the results:

	D	E
1	Name	Scores
2	Smith	7
3	Jones	3
4	Smithfield	1

Is that something you can work with?

[peterh1967a]

Wow Ron,

thank you so much. It took me a while to work out which of my cells corresponded with the formula you provided, but having done that this now works a treat. I would kiss you if I could, but luckily for you you will have to make do with my sincerest thanks.

Peter

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)

[Wavry]

Hi I'm trying to do basically the exact same thing. I have a bunch of Error Codes stored in one column, and each cell can contain either one, or a combination of error codes separated by a |. I thought I was doing it right with a countif statement =COUNTIF(range,"*"& Cell containing the error code I want to count &"*")
But then I realised that it wasn't counting duplicate error codes (some cells might contain the same error code twice, this is by design) so if the same error code appears twice or more times in one cell, it was only counted as one.

[AliGW]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.