Help with summing a range based on a date

[VinceW]

A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the
data. I need a total by month, so I need to know the total for July, August,
etc. Sumproduct works if the data is in 2 columns but not with the dates in
a column and the data in a range.

Your help is appreciated.

A1 B1 C1 D1
Date Acct 1 Acct 2 Acct 3
7/1/09 $56.00 $34.00 $130
7/10/09 $57.00 $76.00 $230
7/13/09 $95.00 $33.00 $630
8/12/09 $78.00 $0.00 $270
8/16/09 $39.00 $79.00 $830
8/31/09 $87.00 $3.00 $278

 

[ryguy7272]

Ok, so go to an adjacent cell, like E2, and paste this formula in:
=TEXT(A2,"mm")
that will give you the month. Fill down to the bottom. Copy the entire
column and paste/special Values over the data in column A (make sure all rows
line up, of course). Make sure you have a column Heading in Column A. Delete
Column E. Click anywhere in the dataset, Data > Pivot Table > Finish. Drag
and drop "Dates" in rows and drag and drop the other stuff in Data area.
Make sure it Sums; sometimes Pivot Tables default to Count and you have to
manually enable the Sum feature.

HTH,
Ryan---

 

[Ashish Mathur]

Hi,

Create a pivot table (Drag date to the row area and amount to the data
area). Now while you are on any cell in the date column, press the pivot
table button the pivot table toolbar and select Group and Show detail >
Group. Select months

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com