month & yr
Posted by bob z on January 03, 2002 6:39 PM
A B
1/10/01 $10
1/12/01 $34
1/2/02 $12
column A has dates column B dollar amounts. I want the totals for jan o1
Posted by Scott on January 03, 2002 7:15 PM
An easy way to do this would to first format your dates so that they are showing only the months. You can do this selecting Format-Cells, then under the number tab, select custom and enter "mmm" in type. Click OK. This will format as Jan, Feb, etc. If you do not want to format over the date, you can add a column and just link the cell over (In cell A1 you would have =B1) and format this. Then you want to subtotal it. Do this by highlighting all of your data including headings (there must be headings for this to work properly). Then select Data-Subtotals. Then make sure "At Each Change" is your date column (the reformatted one), "Use Function" is sum, and "Add Subtotal to" is your quantity column. Click OK, and it will subtotal by month for you.
Posted by Aladin Akyurek on January 04, 2002 1:33 AM
Bob --
=SUMPRODUCT((TEXT(A2:A4,"mmm")="Jan")*(YEAR(A2:A4)=2001),B2:B4)
where A2:A4 houses the dates and B2:B4 the dollar amounts, will give you what you want.
Aladin
===========
