+ Reply to Thread
Results 1 to 12 of 12

count row between each number

  1. #1
    Registered User
    Join Date
    12-20-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    7

    count row between each number

    i have some problem here. maybe someone can help me?

    i have some form used for "deret fauli test" it's like have 42 row cell and 37 column cell.
    what i need is count how much row between number 1to2, 2to3, 3to4, ... 19to20.
    here some example:

    __|A|B |C |D
    1_| 1| | 3 |
    2_| | 2| |
    3_| | | |
    .
    .
    .
    42| | | |4

    each column have 42 row.
    That mean from number 1 to number 2 have 42 + 2 = 44 row (this value put in A 45)
    number 2 to number 3 have 40(because 2 row have add to number 1) + 1 = 41 row (this value put in A 46)
    number 3 to number 4 have 41 + 42 = 83 row. (and this one put in A 47)


    anyone can help me? or maybe with another way to find how much row each number?

    thanks before.
    regards
    kristian

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: count row between each number

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    12-20-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: count row between each number

    this some sample i count row using countif.
    but this method need me to fill number to all cell.
    in sheet 2. this the result i need.

    thanks for reply.
    please help me.
    Attached Files Attached Files

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: count row between each number

    Thanks for the attachment file with description.

    Just one more piece of query, how you are determining the color for each values? Like yellow for 1 and red for 2 like that is there any specific colors needs to be applied or it can be random?

  5. #5
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: count row between each number

    > ... but this method need me to fill number to all cell

    In Sheet1,
    Put instead in F44: =COUNTIF($A$1:$BW$42,A44)
    Copy down
    ------------------------------------
    Any good? wave it, click on the little star at the bottom left of my responses

  6. #6
    Registered User
    Join Date
    12-20-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: count row between each number

    actually, no need color.
    i just need to enter number 1 to 20 (it will random place, and i don't want to put number to all cell because it waste time) but the number must sequence.
    and after enter the number. the form calculate how much cell between the number. that's all i need.

    Max, Singapore. : i thing it's same like my form. i don't want to enter all number to all cell. i just need to enter one number to the end of number.

    see my other attachment.

    number 1 put in B28 (this mean number 1 from A1 to A42, B1 to B28)
    number 2 put in D23 (this mean number 2 from B29 to B42, C1 to C42, D1 to D23)
    number 3 put in F35 (this mean number 3 from D24 to D42, E1 to E42, F1 to F35)

    i need how much cell each number and put it in F44 to F63

    with my form now, it just show 1, because i just enter all number once.
    if count manually, number 1 have 70
    number 2 have 79
    number 3 have 96
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: count row between each number

    Reply to post #6
    Sorry, I'm confused ....
    You have this formula in F44: =COUNTIF($A$1:$BW$42,1)
    I suggested pointing it to A44 instead, ie: =COUNTIF($A$1:$BW$42,A44)
    which then enables you to copy it down easily, without having to manually change the hardcoded 1 to 2, 3, 4, etc

  8. #8
    Registered User
    Join Date
    12-20-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: count row between each number

    sorry i won't use my formula or your formula because it same. it need to enter many 111111111111 22222222222 33333333333....
    i need to enter once and the formula still can count.

    btw thanks to try. but i need another way. if you see my attachment (book2) you see what i mean at christian sheet.

  9. #9
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: count row between each number

    > ... i need to enter once and the formula still can count

    You can use an incrementer term: ROWS($1:1)
    to increase it as you copy down

    In F44: =COUNTIF($A$1:$BW$42,ROWS($1:1))
    Copy down

    p/s: For copying across, use: COLUMNS($A:A)
    ----------------------
    Any luck? Wave it, click the little star at the bottom left of my responses

  10. #10
    Registered User
    Join Date
    12-20-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: count row between each number

    any other way? i don't want use copy or other auto incrementer term.
    have you see my attachment max?
    any formula for that?

  11. #11
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: count row between each number

    Reply to post #10

    If you mean this parts
    > .. number 1 put in B28 (this mean number 1 from A1 to A42, B1 to B28)

    I have no definite solution to offer you, sorry. Generally, formulas work/compute based on cell contents. If the cells contain nothing, how do you expect the formulas to compute? Probably(?) a macro could populate it fully in the matrix based on the shortcut indications that you seek, then only formulas to count can be used.

    FWIW, as a first step I did toy with something like this in say A43, copied across:
    =IF(ISNUMBER(MATCH(TRUE,INDEX(A1:A42<>"",),0)),MATCH(TRUE,INDEX(A1:A42<>"",),0),COUNTBLANK(A1:A42))

  12. #12
    Registered User
    Join Date
    12-20-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: count row between each number

    hmm.. how to describe it.. it like sequence. after A42, then back to B1
    see my attachment how i calculate the example before.
    Attached Files Attached Files

+ 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 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
  2. Replies: 3
    Last Post: 11-18-2013, 02:06 AM
  3. [SOLVED] Filtering macro, count number of rows and include count in last column.
    By Folshot in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2012, 07:17 AM
  4. Count row number and populate column with count number
    By ajay1967 in forum Excel General
    Replies: 3
    Last Post: 12-01-2011, 02:49 PM
  5. Replies: 3
    Last Post: 05-18-2006, 08: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