count number of same characters in a string

anzer

Board Regular
Joined
Mar 5, 2015
Messages
65
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617
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

Board Regular
Joined
Mar 5, 2015
Messages
65
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 :)
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617
Oops! :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

Board Regular
Joined
Mar 5, 2015
Messages
65

ADVERTISEMENT

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 :)
 
Last edited:

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Try

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617

ADVERTISEMENT

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

Board Regular
Joined
Mar 5, 2015
Messages
65
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top