Results 1 to 10 of 10

counting cells between specific values

This is a discussion on counting cells between specific values within the Excel Questions forums, part of the Question Forums category; If I have a column of numbers in which #1 may appear numerous times, I need to count the number ...

  1. #1
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    74

    Default counting cells between specific values

    If I have a column of numbers in which #1 may appear numerous times, I need to count the number of cells between each appearance of #1

  2. #2
    SLA
    SLA is offline
    New Member
    Join Date
    Nov 2012
    Posts
    20

    Default Re: counting cells between specific values

    I'm not one of the site experts... but I had an idea that might work for you using Match

    If you have your column of numbers in Column A, enter the formula =MATCH(1,A2:A19,0)-1 in Column B and copy to the end of your list.

    If you look at the results in Column B you will see that each value of 1 in Column A has a result of 0 in Column B. The value immediately below the 0 in Column B is the number of cells between two instances of 1's in Column A. So I changed the MATCH formula into an IF statement.....

    =IF((MATCH(1,A1:A18,0)-1)=0,B2,"")

    I don't know if there is a way to turn this around so your count total is at the bottom of each group. Hope this gives you some ideas at least.

  3. #3
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    74

    Default Re: counting cells between specific values

    I appreciate your reply. Here is an example of what I am trying to accomplish.

    Last edited by cherokee; Dec 5th, 2012 at 01:38 PM.

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    74

    Default Re: counting cells between specific values

    Example 1 Example 2
    A B C A B C
    ideal ideal
    Formula Formula Formula Formula
    Data Column Column Data Column Column
    1 1 0 0 1 4 3
    2 4 4 2 6 5
    3 10 4 3 3 2
    4 3 1 4 1 3 0
    5 6 2 5 9
    6 1 4 6 7
    7 0 7 5
    8 9 8 15
    9 3 9 1 5
    10 1 4 10 3
    11 6 11 20
    12 1 1 12 1 2
    13 4 13 1 0
    14 12 14 3
    15 1 2 15 7

  5. #5
    SLA
    SLA is offline
    New Member
    Join Date
    Nov 2012
    Posts
    20

    Default Re: counting cells between specific values

    Afraid that is beyond my skill set. I hope someone can help.

  6. #6
    Board Regular MrVillareal's Avatar
    Join Date
    Jun 2011
    Location
    Philippines
    Posts
    496

    Default Re: counting cells between specific values

    in B1 copy down

    =IFERROR(INDEX($C$1:$C$5,IF(A1=1,COUNTIF($A$1:A1,1),"")),"")

  7. #7
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    74

    Default Re: counting cells between specific values

    This recognizes each "1" and places a zero across from each in column B but does not count the number of events between each 1
    Thanks

  8. #8
    Board Regular MrVillareal's Avatar
    Join Date
    Jun 2011
    Location
    Philippines
    Posts
    496

    Default Re: counting cells between specific values

    Excel 2012
    AB
    1DataFormula
    22
    34
    410
    513
    66
    711
    80
    911
    1010
    1110
    126
    1311
    144
    1512
    1612

    xl5galry.xls



    Worksheet Formulas
    CellFormula
    B2=IF(A2=1,IF(A2=1,COUNTIF($A$2:A2,"<>"&1),"")-SUM($B$1:B1),"")

    Last edited by MrVillareal; Dec 6th, 2012 at 09:15 AM.

  9. #9
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    74

    Default Re: counting cells between specific values

    Words maybe powerful, but I cannot find any strong enough to express my thanks for this wonderful formula. It will save me many hours.

    Thank you very much

  10. #10
    Board Regular MrVillareal's Avatar
    Join Date
    Jun 2011
    Location
    Philippines
    Posts
    496

    Default Re: counting cells between specific values

    your most welcome

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com