Count how many times a sequence of characters occurs in a column.

Hi,

You would of course simplify the whole thing if you had three columns, one for each set. Then a simple COUNTIF() function would work, as indeed would a Pivot Table, which might be OTT but is another option.

Thanks for your suggestion, a column for each set would make it simple enough for me to get it done myself
I would need 5 columns though as some mens' matches are best of 5.
Slightly off topic, I even came up with this formula to determine the winner of each set, and it took me a whole day. Would have gotten it done in an hour if I only used a helper column for each set :D

IFERROR(IF(MAXA(LEFT(L29,1),RIGHT(L29,1))=2,"BO3",IF(MAXA(LEFT(L29,1),RIGHT(L29,1))=3,"BO5","")),"")&""&IF(LEFT(L29,1)>RIGHT(L29,1),IF(MID(G29,1,1)>MID(G29,3,1),"WON1","LOST1")&""&IF(MID(G29,5,1)>MID(G29,7,1),"WON2","LOST2")&""&IF(AND(MID(G29,9,1)<>"",(MID(G29,9,1)>MID(G29,11,1))),"WON3",IF(MID(G29,9,1)<>"","LOST3",""))&""&IF(AND(MID(G29,13,1)<>"",(MID(G29,13,1)>MID(G29,15,1))),"WON4",IF(MID(G29,13,1)<>"","LOST4",""))&""&IF(AND(MID(G29,17,1)<>"",(MID(G29,17,1)>MID(G29,19,1))),"WON5",IF(MID(G29,17,1)<>"","LOST5","")),IF(L29="","",IF(MID(G29,1,1)>MID(G29,3,1),"LOST1","WON1")&""&IF(MID(G29,5,1)>MID(G29,7,1),"LOST2","WON2")&""&IF(AND(MID(G29,9,1)<>"",(MID(G29,9,1)>MID(G29,11,1))),"LOST3",IF(MID(G29,9,1)<>"","WON3",""))&""&IF(AND(MID(G29,13,1)<>"",(MID(G29,13,1)>MID(G29,15,1))),"LOST4",IF(MID(G29,13,1)<>"","WON4",""))&""&IF(AND(MID(G29,17,1)<>"",(MID(G29,17,1)>MID(G29,19,1))),"LOST5",IF(MID(G29,17,1)<>"","WON5",""))))

Here's one that uses a couple of helper columns:

IF(NOT(ISERR(FIND("7-6",$G2))),CHOOSE(VALUE(REPLACE($L2,2,2,""))+1,"Loss","Loss","Win"),"")
IF(NOT(ISERR(FIND("6-7",$G2))),CHOOSE(VALUE(REPLACE($L2,2,2,""))+1,"Win","Win","Loss"),"")

Thanks for your solution, I am not familiar with the CHOOSE function I am trying to understand how it works. I have made a mistake though, the sample I gave was not representative as there can be best of 5 matches in mens' tournaments, where the L column (set score) value can be 3-0, 3-1, 3-2(for wins) or 0-3, 1-3, 2-3 (for losses)
Also there can be a case where a player will retire after playing a tiebreak, so the tie break is played but the L column is empty as there is no "set score" to be recorded for that match

So instead of using the L column, the safest choice would be to use this rule:
If A2=Q1 then 7-6 will be a won TB, and 6-7 will be a lost TB
If B2=Q1 then 7-6 will be a lost TB, and 6-7 will be a won TB
This is because the winner of the match is always in the A column, and the loser is in the B column

find the attached file
hope it will help you

see the attached file

see the attachment
i hope it will solve your problem

It did. There was a small mistake but I was able to correct it myself.
Instead of starting from 1,5,9,14,19, it should have been 1,5,9,13,17, otherwise the 4th and 5th sets would have not been counted. So, this is solved and thanks a lot for your time and patience.