SUM Daily to Month

[Jay]

Hi

i appreciate it if you could solve this problem !

i have work sheet with table given below , i have another worksheet
with Monthly sales on that i would like to sum daily sales into
particular month in another sheet 2 whats the formula for this ?


Date Sales
01/01/2006 $1000
02/01/2006 $1200
03/01/2006 $1300
...
..
..
..
31/01/2006 $1200
01/02/2006 $1000
02/02/2006 $1200
..
..
..
28/02/2006 $1500.
..
..
31/12/2006 $1800

Sheet 2:

January ?
February
March
..
..
..
December

thanx in advance
regards
Jay

 

[Dave Peterson]

This will sum the January, 2006 values:

=SUMPRODUCT(--(TEXT(Sheet1!A1:A999,"yyyymm")="200601"),Sheet1!B1:B999)

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=========

You may want to consider using a pivottable to summarize your results.

 

[Jay]

Dave
thank you for ur reply

but it didnt solve my problem its giving error

whats the best way to do it ?

regads
jay

 

[Jay]

Dave
thank you for ur reply

but it didnt solve my problem its giving error

whats the best way to do it ?

regads
jay

 

[Roger Govier]

Hi Jay

Dave's solution should have given you the results for January 2006.
What error are you getting?

Dave's other suggestion for using a pivot table would be far more
preferable (IMHO) as you will get the summary for all months very easily

Place your cursor on the header row
Data>Pivot Table>Next>
Excel should pick up the range of your data in the next dialogue, if it
doesn't mark the range yourself.

Layout>
Drag Date to the Row area
Drag Sales to the Data Area

Double click on Sales to ensure it has picked up Sum of Sales and not
Count
Click OK>Finish

On the sheet where the PT has been Created, right click on the Date
field and select Group By>choose Month AND Year>click OK

You should now see all of your sales summarised by Month.
--
Regards

Roger Govier

Dave
thank you for ur reply

but it didnt solve my problem its giving error

whats the best way to do it ?

regads
jay