Help with a formla that counts 3 consecutive values and outputs 1

[needhelp21]

I need help with a formula that counts 3 consecutive values. However, these values are not numbers. They are letters and are organized across rows, not down columns (ex: one dataset is in C7:BZ7). Each letter is in its own cell. I need to count every occurrence in which "c" appears 3 times consecutively. I need each occurrence of this to output a value of 1. For example, for sdjccoicocccoiccciojc, the output value would be 2, For sdlfkkcccccclkdfklccc, the output values would be 3. I have been attempting for days using sum, countif, and frequency functions, but cannot figure it out. If it would be easier to replace every "c" with a number like "1", I am willing to do so, if someone can PLEASE help me with a formula that works.

Thank you so much for even taking the time to read my question!

[mcmahobt]

Assuming, like you said, your data begins in C7, try this:

Please Login or Register  to view this content.

and copy across.

[needhelp21]

Thank you so much for your advise mcmahobt, unfortunately this did not work. Thanks so much though!

[needhelp21]

Assuming, like you said, your data begins in C7, try this:

Please Login or Register  to view this content.

and copy across.

Does this formula encompass the entire data set on row 7? When input it on C7 turns blue and red, which I believe implies that it is only considering C7. I need it to consider C7:BZ7. I have attempted, unsuccessfully, to add in this range. I am pretty new at excel, trying to learn more, and really appreciate your help!

[Jonmo1]

Do you have an Office 365 subscription, giving you access to the TEXTJOIN function ?

If so try
=(LEN(TEXTJOIN(,,C7:BZ7))-LEN(SUBSTITUTE(TEXTJOIN(,,C7:BZ7),"ccc","")))/3

[needhelp21]

Thank you so much for your help Jonmo1. No, I am working with Office Professional Plus 2016. This did not work for me, but thank you so much! I will attach a version of one of the files I need this to work on for reference.

[Jonmo1]

Then for that formula to work you'd need to concatenate all the cells together into a helper column.

Like in CA7 put
=C7&D7&E7&F7&G7etc...

Then use
=(LEN(CA7)-LEN(SUBSTITUTE(CA7,"ccc","")))/3

[Glenn Kennedy]

=SUMPRODUCT((A1:Q1="c")*(B1:R1="c")*(C1:S1="c"))

adjusted to your ranges will do it. That is probably:

=SUMPRODUCT((C7:BZ7="c")*(D7:CA7="c")*(E7:CB7="c"))

[Jonmo1]

The way I've understood it Glenn, it's not that easy.

In your example, M1:O1 should not be counted, because the M1 and the N1 were already counted in L1:N1.

[needhelp21]

Thank you so much Jonmo1!! it works!! However, now the only problem with using the Concatenate function is that it does not automatically update when new or different information is added to the sheet. Therefore my function does not continue to work as I add in data. Is there a way to change this? Thank you so very much!

[Glenn Kennedy]

Yea. I re-read it, realised I was wrong and have been getting muddled up ever since.

[Jonmo1]

You're welcome.

I can't think of any way to automatically add new ranges to the formula..
Unless you have access to either the TEXTJOIN or CONCAT (this is different from concatenate), but both of those require office 365.

[Glenn Kennedy]

OK. Use this bit of code:

Please Login or Register  to view this content.

with this formula:

=ConcatAll(IF(A1:AZ1<>"",A1:AZ1,""),)

adjust ranges, etc, to suit you, and array enter.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

In combination with Jonmo's formula, you have it. I have to rush. Jonmo, can you finish it by adding your formula??

[needhelp21]

Thank you so much Glenn Kennedy! This also partially works for me, but it does not stop counting 3. It counts cccc as 2, rather than 1. Anything greater than ccc, but less than cccccc, should output 1. Cccccc should out put only 2, the formula you gave me outputs 4 for this. Thank you so much though. I really appreciate it!

[needhelp21]

Thank you so much! I am trying this right now!

[needhelp21]

That is alright. You have been so very helpful! Thank you so much and enjoy the rest of your day!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[needhelp21]

IT WORKS!! Thank you so very much for working together to solve my problem!! I appreciate it so much!!

[needhelp21]

IT WORKS!!! Thank you so very much for working together to solve my problem!! I appreciate it so much!!