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

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

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!

2. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

``Please Login or Register  to view this content.``
and copy across.

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

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

4. Re: Help with a formla that counts 3 consecutive values and outputs 1

Originally Posted by mcmahobt
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!

5. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

6. Re: Help with a formla that counts 3 consecutive values and outputs 1

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.

7. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

8. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

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

9. Re: Help with a formla that counts 3 consecutive values and outputs 1

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.

10. Re: Help with a formla that counts 3 consecutive values and outputs 1

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!

11. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

12. Re: Help with a formla that counts 3 consecutive values and outputs 1

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.

13. Re: Help with a formla that counts 3 consecutive values and outputs 1

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??

14. Re: Help with a formla that counts 3 consecutive values and outputs 1

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!

15. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

16. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

17. Re: Help with a formla that counts 3 consecutive values and outputs 1

You're welcome.

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.

18. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

19. Re: Help with a formla that counts 3 consecutive values and outputs 1

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1