Calculating totals by month from whole date

  • Thread starter Thread starter Marcie4
  • Start date Start date
M

Marcie4

I'm trying to figure out the proper calculation to pull totals by dat
in Excel. Here's my problem:

I have a workbook with 2 worksheets; one is the main data, the other i
the statistics from the main data. Within the main data, there is
column titled "date paid" and another column titled "total paid" (ther
are a bunch more columns, but they don't matter for this problem). Th
"date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O
the stats page, I have columns titled for each month of the year an
would like each to include how much was paid for each month from th
main data worksheet.

For example:
On the main data page I have
date paid total paid
1/1/04 $500
1/15/04 $200
2/3/04 $100
3/30/04 $300

Here's how my stats page SHOULD look, but I can't make it:
January February March
$700 $100 $300

How can I get the totals to come over by month?

Any help would be GREATLY appreciated
 
Hi
one way:
use a pivot table and group by months. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Another way would be to use SUMPRODUCT. If you second sheet contains in
row 1 the month name as 'Text' and you have only data for one year
enter the following in A2 (for January):
=SUMPRODUCT(--(TEXT('data'!$A$1:$A$1000,"MMMM")=A$1),'data'!$B$1:$B$100
0)
and copy to the right
 
Thank you so much for the advise Frank! I chose to use the SUMPRODUC
formula you posted instead of the pivot table since pivot tables don'
update automatically, and I'm not the only one that will be using thi
spreadsheet.

The SUMPRODUCT worked perfectly! I don't know how, but it worked an
that's what matters. I'll delve deeper to find out how it works.

I wish I would have posted my problem yesterday... then I could hav
spent the day playing instead of working!

Thanks again!
Marci
 
Back
Top