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

    When The Mth Changes (Excel 2000)

    Good Morning,

    I have the following code that COUNTS the difference between dates. In other words....if on 6/12, 7 records (rows) were inserted, then on 6/13, 2 records were inserted the cell where the formula for SumForDate is would be 7 then 2. It works great, I would like to know if there is a way to modify it a just a bit so it can recognize the change in the month. i.e. I need it to COUNT all the records that were inserted during the month of June. Once July's date begins...show the total count for June...then at the end of July and August begins, provide the COUNT for July, etc. (the count should start over at 0).

    Is this even possible. I think so since I got the below code to works fine....just not sure how to tell it to recognize a new month.

    Thanks.

    Function CountForDate(rng As Range) As Long
    Application.Volatile
    Dim rngAddr As String
    rngAddr = rng.Address
    CountForDate = (Range(rngAddr, Range(rngAddr).End(xlUp)).Count) - 1
    End Function
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: When The Mth Changes (Excel 2000)

    The function you posted will only work if the workbook has a special structure you haven't told us about. Can you tell us something about the setup of the worksheet?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: When The Mth Changes (Excel 2000)

    Do you have the dates stored in cells?
    If so you could use the array (confirm with ctrl-shift-enter) formula
    =SUM(IF((YEAR($A$1:$A$2500)=2004)*(MONTH($A$1:$A$2 500)=6),1))

    To count the number of cells in the range A1:A2500 (change as desired) that are June 2004 dates.

    For July:
    =SUM(IF((YEAR($A$1:$A$2500)=2004)*(MONTH($A$1:$A$2 500)=7),1))

    etc

    Steve

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

    Re: When The Mth Changes (Excel 2000)

    Hans,

    The only thing is a formula: =IF(P22<>"",CountForDate(P22),""). I put this formula in column "Q". Once the date is entered in a cell in Column "P", then the total is provided in "Q". That's the only special thing.

    Steve,

    Wouldn't your option have to be entered each time or rather the F9? If so, I'm trying to avoid this, I would rather that once the date is entered the COUNT is provided.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When The Mth Changes (Excel 2000)

    I think that this modification to Steve's array formula does what you want:

    <pre>=SUM(IF((YEAR($A$1:$A$2500)=2004)*(MONTH($A$1 :$A$2500)=MONTH(NOW())-1),1))
    </pre>

    Legare Coleman

Posting Permissions

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