# Thread: Date, Sum Question (2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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>

6. ## 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>

7. ## 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
•