Help with summing a range based on a date
 

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

Help with summing a range based on a date
 

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---




--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"VinceW" wrote:

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

Help with summing a range based on a date
 

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

"VinceW" wrote in message
...
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