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

--
Message posted from http://www.ExcelForum.com

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


--
Regards
Frank Kabel
Frankfurt, Germany


> I'm trying to figure out the proper calculation to pull totals by
date
> in Excel. Here's my problem:
>
> I have a workbook with 2 worksheets; one is the main data, the other
> is the statistics from the main data. Within the main data, there is
a
> column titled "date paid" and another column titled "total paid"
> (there are a bunch more columns, but they don't matter for this
> problem). The "date paid" column will contain dates such as 1/1/04,
> 5/15/04 etc. On the stats page, I have columns titled for each month
> of the year and would like each to include how much was paid for each
> month from the 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!
>
>
> ---
> Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

Hi Marcie
as explanantion see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany


> Thank you so much for the advise Frank! I chose to use the SUMPRODUCT
> formula you posted instead of the pivot table since pivot tables
don't
> update automatically, and I'm not the only one that will be using
this
> spreadsheet.
>
> The SUMPRODUCT worked perfectly! I don't know how, but it worked and
> 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 have
> spent the day playing instead of working!
>
> Thanks again!
> Marcie
>
>
> ---
> Message posted from http://www.ExcelForum.com/