+ Reply to Thread
Results 1 to 4 of 4

Consecutive Counts

  1. #1
    Registered User
    Join Date
    01-15-2009
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    2

    Consecutive Counts

    Hi there,

    I have a sheet that lists customers in column A and then each customers Distribution & Sales over a period of time. The spreadsheet then flags if the customer has sold out in any month. I now require a count of maximum consecutive sellouts for each customer. For example:

    Customer A S/O S/O nil nil S/O
    Customer B S/O S/O S/O nil nil
    Customer C nil S/O S/O S/O S/O

    In the above example Customer A would have 2, B 3 and C 4 sellouts. I'm looking at about 10K customers I require for each product.

    Hope someone can help!
    Last edited by Jarbugs; 01-22-2009 at 12:37 AM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Counting max consecutive occurrences of a value

    With your sample data in A1:F3

    This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER (instead
    of just ENTER) returns the max consecutive occurrences of "s/o" for
    Customer A

    Please Login or Register  to view this content.
    Copy I1 and paste into I2 and down as far as you need.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    12-15-2008
    Location
    Singapore
    Posts
    38
    Fantastic. May I understand the logic behind the last part of the formula?
    IF(B1:H1<>"s/o",COLUMN(B1:H1))

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    How FREQUENCY works in this situation

    The FREQUENCY function rounds test values UP to the next "bin" value, ignoring text and TRUE/FALSE values.

    Example:

    Please Login or Register  to view this content.

    I hope that helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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