Results 1 to 6 of 6

Thread: Counting Data

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Data

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Excel 2000.

    Hello,

    I will attempt to make this simple....

    A2..A500 contains data (each cell WILL contain data)
    M2..M500 will contain DATES (NOT every cell will contain a date)

    For example: M5 might have 6/24/01 but M6..M10 wont contain a date..M11 will have 6/25/01.

    What I am attempting to do is create a formula (or function) and put it in N5..therefore when N5 sees there is a date in M5 start counting all the values beginning with A5 and stop counting at A10 because the date changes in M11 ...in this case the count (total) in N5 would be 5. The count starts over beginning in A11 because the new date is in M11...and so on.

    If this is too confusing, please post back and I will try to make it more clear.

    I appreciate any suggestions.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting Data

    Roberta,

    The following will do what you want (or what I think you want), provided that all the cells between the different dates are blank, and contain not even a space. A useful way to ensure that there are no spurious spaces in a rang is to use conditional formatting that will highlight them, should they happen.

    The formatting to apply is :
    Cell Value Is greater than or equal to =" ", and then set the background color to something that will highlight the cell.

    You can apply that conditional formatting to M1:M500, or whatever the extent of the relevant range is.

    Function CountForDate(rng As Range) As Long
    Application.Volatile
    Dim rngAddr As String
    rngAddr = rng.Address
    CountForDate = (Range(rngAddr, Range(rngAddr).End(xlDown)).Count) - 1
    End Function

    Hope the above assists you,

    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Data

    Good Morning Andrew,

    I appreciate your help with this...one thing tho and that's cause i'm a dummy...but what do I do with the code? I put it in vb and created a function but nothing is happening...

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting Data

    Did you place the code in a general module in the workbook involved ? To that you should go too the VB editor (Alt F11), and Insert, Module, and paste the code there. When doing that make sure the workbook you are working on is highlighted in the Project window (usually on the right). It will be referred to as VBAProject(Bookname), where Bookname is the name of the workbook involved.

    In N2 you need to enter the formula =IF(M2<>"",CountForDate(M2),""), and copy it down. It will be necessary to put some value in the last+1 cell in column M, e.g. if the last used row is 500, you need a value in M501.

    Hope the above is clear to you.

    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Data

    Just in case you're after a way to do this without code, the simplest method I can think of is to put "=IF(M2<>0,M2,O1)" in o2 and "=SUMIF(O:O,M1,A:A)" in p2, then copy the two formulas down for the entire length of your data. You could hide colum o if you didn't want to see these dates.

    HTH

    Brooke

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Data

    Good Morning,

    I tried Andrew's first and it worked perfectly...however, I wanted to test Brooke's suggestion as well and it worked perfectly too.

    Thanks to both of you for your quick and accurate solution.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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