+ Reply to Thread
Results 1 to 8 of 8

Ignore Blank in Counting Consecutive Values

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

    Ignore Blank in Counting Consecutive Values

    I have a formula that calculates a count of consecutive cells:

    UUUUUUPPUUUUUUEUUU

    So the values returned would be 15U, 2P, and 0E since there are no consecutive Es.

    That all works fine, but now I have to find a way to ignore if there is a blank space and have it still count:

    UUUU UUPPUUUUUE UUU

    I would still need it to give me the same values as the first line, ignoring that there is a blank.

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

    Re: Ignore Blank in Counting Consecutive Values

    Will it help if I provide the current formula?
    =SUM(IF(FREQUENCY(IF(H10:AB10="E",COLUMN(H10:AB10)),IF(H10:AB10<>"E",COLUMN(H10:AB10)))>=11,FREQUENCY(IF(H10:AB10="E",COLUMN(H10:AB10)),IF(H10:AB10<>"E",COLUMN(G10:AB10)))))

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Ignore Blank in Counting Consecutive Values

    UUUU UUPPUUUUUE UUU
    Is it in one cell?
    Quang PT

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

    Re: Ignore Blank in Counting Consecutive Values

    No, each letter is in it's own cell.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Ignore Blank in Counting Consecutive Values

    If I understand correctly this formula in E2 filled down and across as in below.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    1
    U P E
    2
    15
    2
    0
    3
    14
    2
    0
    4
    5
    6
    U U U U U U P P U U U U U U E U U U
    7
    U U U U U U P P U U U U U E U U U
    Dave

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Ignore Blank in Counting Consecutive Values

    Double posted.

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

    Re: Ignore Blank in Counting Consecutive Values

    I'm not sure that would work.

    The issue is that I only need certain values counted if they are consecutive 6 or more.
    So the formula I have now only calculates U if there are 6 or more consecutively. It will ignore all others. BUT I also need it to recognize if there is blank cell in the middle of those 6 and still calculate them-essentially ignoring the blank space.
    So U U U U U U would be 6
    But U U U U U U would also be 6. Right now the blank space causes a problem because it only sees it as 3 consecutive and won't return a value.

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

    Re: Ignore Blank in Counting Consecutive Values

    There is supposed to be spaces between that second line.
    U U U U U U should return 6

    U U U U U U need this to return 6 too.

+ 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] Counting consecutive values less than X
    By stellards20 in forum Excel General
    Replies: 3
    Last Post: 06-18-2014, 12:08 AM
  2. [SOLVED] Counting Consecutive Values
    By khauskins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2012, 11:37 AM
  3. Replies: 4
    Last Post: 07-05-2012, 12:05 PM
  4. Counting consecutive values
    By Elijah in forum Excel General
    Replies: 7
    Last Post: 04-23-2010, 05:13 PM
  5. Counting consecutive blank cells?
    By Roochuck in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2008, 12:24 AM
  6. Counting consecutive non-zero values
    By RichH6109 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-26-2007, 07:21 PM
  7. Counting consecutive blank cells
    By KevinE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2005, 09:40 AM

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