Help with summing a range based on a date

V

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
 
R

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top