count number of same characters in a string

[anzer]

Hello,

I'm quiting smoking after 10 years! (I'm 25 and it's been 5 days in a row now!! )
and to help me I created an excel file with a calendar
When I smoke I put F below the date and X if I don't smoke. I want a formulas that count the number of X in a row example:

F F X F F X F X X X X X ( each letter are in a different cell and I want the result to be 5 not 7 )

Any idea ?!

Thank you

 

[Eric W]

Congratulations and good luck!

If the range where you put the Fs and Xs is A1 to ZZ1, then this could work:

=COUNTIF(OFFSET($A$1,0,LOOKUP(2,1/($A$1:$ZZ$1="F"),COLUMN($A$1:$ZZ$1))):$ZZ$1,"X")

change the range as needed.

Hope this helps!

 

[anzer]

Hello,

thank you for helping me out

Unfortunately results is 3 and not 5 so i guess it takes the number of occurence that X appear instead of the longest consucutive string of X
Also my excel file goes like this:

C9 is Januray 1st to Feb 18 AY9 and i write fs and Xs below (C10 to AY10)

Thank you

 

[Eric W]

Oops!

I didn't allow for the possibility that you didn't start in column A. This should work better:

=COUNTIF(OFFSET($C$10,0,LOOKUP(2,1/($C$10:$ZZ$10="F"),COLUMN($C$10:$ZZ$10)-COLUMN($C$10)+1)):$ZZ$10,"X")

 

[anzer]

Hello,

It's working!

Just want to know something also if I put F today (i won't, promise!!) it will go back at 0 but i want to see thru the year what was my longest string of none smoking
example: if i smoke a cigarette the 31st of december (with a couple drinks we never know) then it will give 0 but during the year i had 350 day in a row of none smoking?

Thank you

 

[63falcondude]

Try

=MAX(FREQUENCY(IF(C10:AY10="X",COLUMN(C10:AY10)),IF(C10:AY10="F",COLUMN(C10:AY10)))) Ctrl Shift Enter

 

[Eric W]

This should give you the longest string of no smoking:

=MAX(FREQUENCY(IF(C10:ZZ10="x",COLUMN(C10:ZZ10)),IF(C10:ZZ10<>"x",COLUMN(C10:ZZ10))))

and press Control+Shift+Enter instead of just Enter when you enter it in the formula bar.

 

[anzer]

Thank you Eric, it is working perfectly!!!!

 

[anzer]

Same formulas as Eric and it is working perfectly! Thank you to you too!!! and after 5 days it is way easier than i thought

 

[Eric W]

Glad we could help, and keep it up!