Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a rolling 6 month average? | Excel Discussion (Misc queries) | |||
How do I extract the month end value from a daily time series? | Excel Discussion (Misc queries) | |||
Month to date sales - reset in new month??? | Excel Worksheet Functions | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions |