+ Reply to Thread
Results 1 to 19 of 19

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

  1. #1
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

    Red face 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. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    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.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

    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. #4
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

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

    Quote Originally Posted by mcmahobt View Post
    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. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #6
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

    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. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

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

    =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"))
    Attached Files Attached Files
    Glenn



  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #10
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    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. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    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. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

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

  14. #14
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

    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. #15
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

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

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

  16. #16
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

    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. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

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

    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.

  18. #18
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

    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. #19
    Registered User
    Join Date
    06-05-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    10

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] sum of consecutive counts without blanks
    By makinmomb in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-23-2017, 03:58 PM
  2. Get unique values from column and get Counts and Sums based on these values
    By khhleung in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2014, 09:58 PM
  3. [SOLVED] Counts two consecutive number???
    By thangkhi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2013, 01:07 PM
  4. [SOLVED] Count consecutive non-zero values; list first non-zero value; display counts as an array
    By treznick in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-17-2012, 08:25 PM
  5. Conditional outputs based on 2 values
    By calador in forum Excel General
    Replies: 11
    Last Post: 01-19-2012, 11:00 AM
  6. Consecutive Counts
    By Jarbugs in forum Excel General
    Replies: 3
    Last Post: 01-19-2009, 09:57 AM
  7. Sumif outputs values
    By chrisc in forum Excel General
    Replies: 4
    Last Post: 10-27-2005, 10:05 AM

Tags for this Thread

Bookmarks

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