+ Reply to Thread
Results 1 to 5 of 5

Need to count consecutive letters and then deduct lowecase

  1. #1
    Registered User
    Join Date
    11-12-2015
    Location
    Colorado
    MS-Off Ver
    2010
    Posts
    10

    Need to count consecutive letters and then deduct lowecase

    I have a fairly complicated formula that counts consecutive letters if they are consecutive 6 or greater.

    The formula (array) is this:
    =SUM(IF(FREQUENCY(IF(H12:AB12="U",COLUMN(H12:AB12)),IF(H12:AB12<>"U",COLUMN(H12:AB12)))>=6,FREQUENCY(IF(H12:AB12="U",COLUMN(H12:AB12)),IF(H12:AB12<>"U",COLUMN(H12:AB12)))))

    The line would look like this:

    U U U U U U U U U U U U and the return would be 12.

    Now though I would need it to deduct a lowecase "u" if it appears.
    So I still want it to count this as consectutive and give me 12, but in the next column I want to put a formula that deducts the lower case "u"s that appear in that string.

    U U U U U u U U U u U U so the return would be 10.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Need to count consecutive letters and then deduct lowecase

    Try this arrayed formula

    =SUM(IF(FREQUENCY(IF(EXACT(H12:AB12,"U"),COLUMN(H12:AB12)),IF(NOT(EXACT(H12:AB12,"U")),COLUMN(H12:AB12)))>=6,FREQUENCY(IF(EXACT(H12:AB12,"U"),COLUMN(H12:AB12)),IF(NOT(EXACT(H12:AB12,"U")),COLUMN(H12:AB12)))))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    11-12-2015
    Location
    Colorado
    MS-Off Ver
    2010
    Posts
    10

    Re: Need to count consecutive letters and then deduct lowecase

    That did not seem to deduct the lowercase, still gave me the same total.

  4. #4
    Registered User
    Join Date
    11-12-2015
    Location
    Colorado
    MS-Off Ver
    2010
    Posts
    10

    Re: Need to count consecutive letters and then deduct lowecase

    Actually what that seems to be doing is only counting consecutive upper case greater than six.
    I need it to count consecutive upper and lower that are greater than 6 in any combination. But then from those sets deduct the lower case.

    This could be in two formulas if it needs to be. The first one being the formula above that I am already using and then a second column that takes those results and deducts the lower case.
    Not sure what is best....

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Need to count consecutive letters and then deduct lowecase

    Actually, that formula will count consecutive capital "U" which is not what you want either.

+ 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] Sort on whether cell contains 2 consecutive capital letters
    By greek in forum Excel General
    Replies: 12
    Last Post: 06-16-2014, 08:07 AM
  2. [SOLVED] Count number of occurrences but if there are consecutive matches count them as one
    By michaljireht in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-26-2013, 08:26 PM
  3. Replies: 13
    Last Post: 07-08-2012, 11:26 PM
  4. Replies: 17
    Last Post: 05-20-2011, 01:48 AM
  5. Text to Coloumns, by consecutive capital letters
    By paperclip in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-20-2006, 07:38 AM
  6. [SOLVED] Count Intervals of 2 Consecutive Values in same Row and Return Count across Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-29-2005, 11:30 AM
  7. How do i count numbers and letters to find a total count of all
    By Linda in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-10-2005, 12:55 PM

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