SUM Daily to Month
 

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

SUM Daily to Month
 

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 he
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 wrote:

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

SUM Daily to Month
 

Dave
thank you for ur reply

but it didnt solve my problem its giving error

whats the best way to do it ?

regads
jay
Dave Peterson wrote:
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 he
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 wrote:

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

SUM Daily to Month
 

Dave
thank you for ur reply

but it didnt solve my problem its giving error

whats the best way to do it ?

regads
jay
Dave Peterson wrote:
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 he
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 wrote:

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

SUM Daily to Month
 

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
DataPivot TableNext
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 OKFinish

On the sheet where the PT has been Created, right click on the Date
field and select Group Bychoose Month AND Yearclick OK

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

Roger Govier


"Jay" wrote in message
oups.com...
Dave
thank you for ur reply

but it didnt solve my problem its giving error

whats the best way to do it ?

regads
jay
Dave Peterson wrote:
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 he
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 wrote:

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