+ Reply to Thread
Results 1 to 8 of 8

Count frequency of consequitive numbers

  1. #1
    Forum Contributor
    Join Date
    08-08-2012
    Location
    USA, CA
    MS-Off Ver
    Excel 2007
    Posts
    131

    Count frequency of consequitive numbers

    I need to count the number of occurences from a set of numbers.

    For example:

    1
    1
    1
    0
    1
    1
    1
    1
    0
    1
    1

    The total number of occurences should be 3 because it should count consecutive occurences. Any gaps such as 0 will create a new set of occurences that group the numbers into 1 occurence. I hope that makes sense.

    111 = 1
    0
    1111 = 1
    0
    11= 1

    Total = 3

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count frequency of consequitive numbers

    Try this array formula**:

    =COUNT(1/FREQUENCY(IF(A1:A11=1,ROW(A1:A11)),IF(A1:A11<>1,ROW(A1:A11))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    08-08-2012
    Location
    USA, CA
    MS-Off Ver
    Excel 2007
    Posts
    131

    Re: Count frequency of consequitive numbers

    It shows up as 0 when I input the formula. Does the frequency array need a range or does the if formula take over the frequency value? I think we are close here. I like the way that formula looks and it sort of makes sense.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count frequency of consequitive numbers

    Did you array enter the formula?

    Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

    When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

  5. #5
    Forum Contributor
    Join Date
    08-08-2012
    Location
    USA, CA
    MS-Off Ver
    Excel 2007
    Posts
    131

    Re: Count frequency of consequitive numbers

    Nice, I actually tried applying this on a pivot however it didn't work. Also the pivot number format is custom to "1" and not a true sum. I also have blank values. Is there any way around this???

    Thank you, Appreciate your help.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count frequency of consequitive numbers

    Never use pivot tables.

    If the data was in a normal range empty cells are evaluated as not part of a streak.

    This example has 3 groups of 1s:

    Data Range
    A
    B
    1
    0
    2
    0
    3
    1
    1
    4
    5
    1
    2
    6
    1
    7
    1
    8
    1
    9
    0
    10
    ------
    ------
    11
    1
    3

  7. #7
    Forum Contributor
    Join Date
    08-08-2012
    Location
    USA, CA
    MS-Off Ver
    Excel 2007
    Posts
    131

    Re: Count frequency of consequitive numbers

    If the streak contains 2s and 3s (not just 1s), how do I add multiple values in the formula?

    =COUNT(1/FREQUENCY(IF(OR(B23:O23=1,B23:O23=2,B23:O23=3),COLUMN(B23:O23)),IF(OR(B23:O23<>1,B23:O23<>2),COLUMN(B23:O23))))
    Last edited by newbie4; 07-16-2014 at 06:57 PM.

  8. #8
    Forum Contributor
    Join Date
    08-08-2012
    Location
    USA, CA
    MS-Off Ver
    Excel 2007
    Posts
    131

    Re: Count frequency of consequitive numbers

    Also, I switched the formula rows to columns.

    Does anyone know how to pull in for example multiple numbers for count frequency of consecutive numbers?

    Thank you,
    N

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count frequency of consequitive numbers

    Quote Originally Posted by newbie4 View Post
    If the streak contains 2s and 3s (not just 1s), how do I add multiple values in the formula?

    =COUNT(1/FREQUENCY(IF(OR(B23:O23=1,B23:O23=2,B23:O23=3),COLUMN(B23:O23)),IF(OR(B23:O23<>1,B23:O23<>2),COLUMN(B23:O23))))
    Like this...

    =COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH(B23:O23,{1,2,3},0)),COLUMN(B23:O23)),IF(ISNA(MATCH(B23:O23,{1,12,3},0)),COLUMN(B23:O23))))

    Still array entered!

+ 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] Count If Function To Count Frequency Of Long Numbers
    By RowanB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2013, 05:18 AM
  2. Need Help with Macro to count the frequency of numbers in multiple columns and rows
    By crissycr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2012, 02:11 AM
  3. Using FREQUENCY to sum instead of count
    By Emma_Fairclough in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2008, 01:33 PM
  4. Frequency count
    By topgunnerp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-30-2007, 03:06 PM
  5. [SOLVED] Count the frequency of all numbers in a column
    By horatio in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-16-2005, 10:06 PM

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