Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date, Sum Question (2000)

    Greetings All,
    I have a spread sheet, handed down from previous person, to keep track of medical items. My question, I have one column that shows a date and a quantity issued. In another column I show how many of the item were issued during a month. I have to manually add the totals to the months columns. Is there a formula to use so the issued items, based on the date, will fill in the total for the appropriate month?
    I hope this makes sense. Thanks for every ones help.

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

    Re: Date, Sum Question (2000)

    You could use a pivot table to sum the number of issued items by month: you can group a date column by month in a pivot table.
    It is also possible to use SUMPRODUCT formulas to sum the numbers.
    If you want assistance with either, it would be helpful if you could post a small sample workbook; replace sensitive or proprietary data with dummy data if necessary.

  3. #3
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date, Sum Question (2000)

    Hans,
    Attached is the spreadsheet that I am using. As stated this was created before my use by someone else. All I am doing is tweaking it for my needs and make it do what I don't need to do. Hopefully you can see my question.
    Thanks.

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

    Re: Date, Sum Question (2000)

    Enter the following formula in B7:
    <code>
    =SUMPRODUCT((MONTH($D$8:$D$125)=ROW()-6)*$E$8:$E$125)
    </code>
    and fill down to B18

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date, Sum Question (2000)

    [Edited to correct formula - original was counting entries, not summing quantities]


    Hi,
    Your biggest problem will be the fact that the dates in the worksheet are not actually stored as dates, but as text. If they were entered as dates, it would be fairly easy to use a sumproduct formula to do what you want:
    <code>ODUCT((TEXT($D$8:$D$125,"mmmm")=A7)*$E$8:$E$ 125)</code>

    in B7 and copy down
    HTH

    PS It would be easier if we didn't have to break the password protection on your worksheet in order to work with it! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date, Sum Question (2000)

    I stand corrected - you don't actually need to convert the dates to "real" dates for either method to work! That'll teach me to make assumptions! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Date, Sum Question (2000)

    I was surprised too - I just tried it out to see whether it would work, and it did! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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