How to sum by month
in [Functions]
|
Prev: personal.xls
Next: IF STATEMENT
From: Charles on 21 May 2010 17:36 I have a sales order spreadsheet that gets updated daily from our ERP system. The worksheet has the following columns: 1. Customer code (each customer could have several entries per month) 2. Order date (the current worksheet has two years of history in addition to the daily updates) 3. Amount What I would like to do is make a new worksheet that has the following columns: 1. Customer code listed once for each customer 2. Total sales summed by month ( so I would have a column for each 2 years of history and will add each new month) I know I could do this with a pivot table but for internal company reasons I would like to know if anyone can tell me how to do it with a formula. Thanks in advanced for your help!!! Charles
From: Dave Peterson on 21 May 2010 18:53 This sounds like the perfect opportunity to learn about pivottables. Make sure your data has a header row. Say your data is in A1:C999 Select your data (a1:C999) Data|Pivottable (in xl2003 menus) Follow the wizard until you get to the step with the Layout button on it. Click the Layout button Drag the header for the custcode to the row field drag the header for the date to the row field drag the header for the amount to the data field If the amount says "count of", double click on it and change it to "sum of". Finish up the wizard. You'll have a nice summary table, but with entries for each date. So rightclick on the Date header Choose Group and show details Then choose Group Group by Months (or Years and Months???) The nice thing about the pivottable is that you can rearrange it to create different views into the data. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx Charles wrote: > > I have a sales order spreadsheet that gets updated daily from our ERP system. > The worksheet has the following columns: > 1. Customer code (each customer could have several entries per month) > 2. Order date (the current worksheet has two years of history in addition to > the daily updates) > 3. Amount > > What I would like to do is make a new worksheet that has the following > columns: > 1. Customer code listed once for each customer > 2. Total sales summed by month ( so I would have a column for each 2 years > of history and will add each new month) > > I know I could do this with a pivot table but for internal company reasons I > would like to know if anyone can tell me how to do it with a formula. > > Thanks in advanced for your help!!! > Charles -- Dave Peterson
From: Matt's Dad on 22 May 2010 16:07 Since you don't want to use pivot tables, you can accomplish a similar result by using SUMPRODUCT: FORMULA (In B2): =SUMPRODUCT(--(B$1>=($B$8:$B$24)),--(DATE(YEAR(B$1),MONTH(B$1),1)<=($B$8:$B$24)),--($A2=($A$8:$A$24)),$C$8:$C$24) To get an understanding of how this function works you can read this article: http://www.journalofaccountancy.com/Issues/2009/Jul/20091493 Dates in B1 through D1 are month-end dates. If you only want to see the month on your reports ("January" instead of "Jan-10") you can use custom formatting: Format-Cells-Number tab-Custom and in the Type box put "mmmm". If you want only month and year: "mmm yyyy" ANSWER: A B C D E 1 Code Jan-10 Feb-10 Mar-10 Total 2 12345 3 21 38 62 3 12346 3 21 27 51 4 12347 - 7 - 7 Total 6 49 65 120 DATA: A B C 8 Code Date Amount 9 12345 01/15/10 1 10 12345 01/15/10 2 11 12346 01/15/10 3 12 12346 02/15/10 4 13 12345 02/15/10 5 14 12345 02/15/10 6 15 12347 02/15/10 7 16 12346 02/15/10 8 17 12346 02/15/10 9 18 12345 02/15/10 10 19 12345 03/15/10 11 20 12345 03/15/10 12 21 12346 03/15/10 13 22 12346 03/15/10 14 23 12345 03/15/10 15 TOTAL 120 "Charles" <Charles(a)discussions.microsoft.com> wrote in message news:C20E648E-35BD-485E-BF99-68A3E0EB8B84(a)microsoft.com... >I have a sales order spreadsheet that gets updated daily from our ERP >system. > The worksheet has the following columns: > 1. Customer code (each customer could have several entries per month) > 2. Order date (the current worksheet has two years of history in addition > to > the daily updates) > 3. Amount > > What I would like to do is make a new worksheet that has the following > columns: > 1. Customer code listed once for each customer > 2. Total sales summed by month ( so I would have a column for each 2 years > of history and will add each new month) > > I know I could do this with a pivot table but for internal company reasons > I > would like to know if anyone can tell me how to do it with a formula. > > Thanks in advanced for your help!!! > Charles > >
From: Dave Peterson on 22 May 2010 16:54 Whoops! I didn't see read closely enough. Dave Peterson wrote: > > This sounds like the perfect opportunity to learn about pivottables. > > Make sure your data has a header row. > Say your data is in A1:C999 > Select your data (a1:C999) > Data|Pivottable (in xl2003 menus) > Follow the wizard until you get to the step with the Layout button on it. > > Click the Layout button > Drag the header for the custcode to the row field > drag the header for the date to the row field > drag the header for the amount to the data field > > If the amount says "count of", double click on it and change it to "sum of". > > Finish up the wizard. > > You'll have a nice summary table, but with entries for each date. > > So rightclick on the Date header > Choose Group and show details > Then choose Group > Group by Months (or Years and Months???) > > The nice thing about the pivottable is that you can rearrange it to create > different views into the data. > > If you've never used pivottables, here are a few links: > > Debra Dalgleish's pictures at Jon Peltier's site: > http://peltiertech.com/Excel/Pivots/pivottables.htm > And Debra's own site: > http://www.contextures.com/xlPivot01.html > > John Walkenbach also has some at: > http://j-walk.com/ss/excel/files/general.htm > (look for Tony Gwynn's Hit Database) > > Chip Pearson keeps Harald Staff's notes at: > http://www.cpearson.com/excel/pivots.htm > > MS has some at (xl2000 and xl2002): > http://office.microsoft.com/downloads/2000/XCrtPiv.aspx > http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx > > Charles wrote: > > > > I have a sales order spreadsheet that gets updated daily from our ERP system. > > The worksheet has the following columns: > > 1. Customer code (each customer could have several entries per month) > > 2. Order date (the current worksheet has two years of history in addition to > > the daily updates) > > 3. Amount > > > > What I would like to do is make a new worksheet that has the following > > columns: > > 1. Customer code listed once for each customer > > 2. Total sales summed by month ( so I would have a column for each 2 years > > of history and will add each new month) > > > > I know I could do this with a pivot table but for internal company reasons I > > would like to know if anyone can tell me how to do it with a formula. > > > > Thanks in advanced for your help!!! > > Charles > > -- > > Dave Peterson -- Dave Peterson
From: Harlan Grove on 22 May 2010 17:09
"Matt's Dad" <Aspen2...(a)verizon.net> wrote... .... >=SUMPRODUCT(--(B$1>=($B$8:$B$24)),--(DATE(YEAR(B$1),MONTH(B$1),1)<=($B$8:$B$24)), >--($A2=($A$8:$A$24)),$C$8:$C$24) .... You could replace the first 2 terms with --(TEXT(B$1,"YYYYMM")=TEXT($B$8:$B$24,"YYYYMM")) |