How to sum by month
in [Functions]
|
Prev: personal.xls
Next: IF STATEMENT
From: kathy on 24 May 2010 10:21 Would your formula(or something like it) work for the problem I am having? End result is to compare prior year numbers to current year to date sheet 1 has the following columns-- sheet 2 has history-each month totals for a number of years COL A Secured Loan Interest COL B(current month -column title Mar-10) 39,333.98 COL C(prior month -column title Feb-10) $36,134.55 COL E(current YTD) $113,440.47 COL F(previous YTD ) $512,814.15 All of the information on worksheet 1 is filled in with a vlookup/match formula. ex. =VLOOKUP($A5,History1!$A$1:$CG$176,MATCH('Income Variance'!$C$3,History1!$A$1:$CG$1,0),0 I want COL F to add up the totals from sheet 2(called History1) for each month up to the current month for this year, so that this year and prior year totals are for the same timeframe. -- Thank you, Kathy "Matt's Dad" wrote: > 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: Roger Govier on 24 May 2010 12:34 Hi Kathy I am assuming your History1 sheet has columns for many years and that all of the column headers have dates like 01 Mar 2010, but displayed as Mar-10 I would insert a new row 1 on this sheet and in that row place the Year number it the column where the Year starts. In my dummy data I had Jan-09 in B2, Feb-09 in C2 etc, and I entered 2009 in B1 and 2010 in N1 My data only extended on History1 form A1:G20 (amend the formulae below to suit your ranges) I had the name Total in A2 of Sheet1, and in A3 of History1 In B2 of Sheet1 =INDEX(History1!$A$2:$R$21,MATCH($A2,History1!$A$2:$A$21,0),MATCH(B$1,History1!$A$2:$R$2,0)) Copy across to C2 In D2 =SUM(INDEX(History1!$A$1:$Q$20,MATCH($A2,History1!$A$1:$A$20,0),MATCH(YEAR(B$1),History1!$A$1:$Q$1,0)): INDEX(History1!$A$1:$Q$20,MATCH($A2,History1!$A$1:$A$20,0),MATCH(B$1,History1!$A$2:$Q$2,0))) in E2 =SUM(INDEX(History1!$A$1:$Q$20,MATCH($A2,History1!$A$1:$A$20,0),MATCH(YEAR($B$1)-1,History1!$A$1:$Q$1,0)): INDEX(History1!$A$1:$Q$20,MATCH($A2,History1!$A$1:$A$20,0),MATCH($B$1,History1!$A$2:$Q$2,0)))-D2 In C1 of Sheet1 I put the formula =Date(Year(B1),month(B1)-1,1) and formatted the cell as mmm-yy As you change the Current Month in B2, so the values will alter. -- Regards Roger Govier kathy wrote: > Would your formula(or something like it) work for the problem I am having? > End result is to compare prior year numbers to current year to date > sheet 1 has the following columns-- sheet 2 has history-each month totals > for a number of years > > COL A Secured Loan Interest > COL B(current month -column title Mar-10) 39,333.98 > COL C(prior month -column title Feb-10) $36,134.55 > COL E(current YTD) $113,440.47 > COL F(previous YTD ) $512,814.15 > All of the information on worksheet 1 is filled in with a vlookup/match > formula. ex. =VLOOKUP($A5,History1!$A$1:$CG$176,MATCH('Income > Variance'!$C$3,History1!$A$1:$CG$1,0),0 > I want COL F to add up the totals from sheet 2(called History1) for each > month up to the current month for this year, so that this year and prior year > totals are for > the same timeframe. > >
From: kathy on 24 May 2010 17:05 Not sure I follow you. On sheet one rows 5-100 have row titles ie. secured loans,personal loan, etc. right now C5-100 and C5-100 have lookup (=VLOOKUP($A5,History1!$A$2:$CG$177,MATCH('Income Variance'!$B$3,History1!$A$2:$CG$2,0),0)which pulls the current months numbers from the History1 tab. Are you saying that I replace my vlookup with the =index you sent for B2(or B5 in my case) to pull current months numbers. Right now D5 has the difference between last month and this month and E5 pulls the total for the year with this lookup (=VLOOKUP($A5,History1!$A$2:$CG$177,MATCH('Income Variance'!$E$3,History1!$A$2:$CG$2,0),0) Column F is where I need the totals from prior year 2009 from Jan-current month. Should I replace all the vlookups to the =index you sent? Sorry if I am confusing you, but I have never used an =index before so amy not positive I understand. -- Thank you, Kathy "Roger Govier" wrote: > Hi Kathy > > I am assuming your History1 sheet has columns for many years and that > all of the column headers have dates like 01 Mar 2010, but displayed as > Mar-10 > I would insert a new row 1 on this sheet and in that row place the Year > number it the column where the Year starts. > In my dummy data I had Jan-09 in B2, Feb-09 in C2 etc, and I entered > 2009 in B1 and 2010 in N1 > > My data only extended on History1 form A1:G20 (amend the formulae below > to suit your ranges) > I had the name Total in A2 of Sheet1, and in A3 of History1 > > In B2 of Sheet1 > =INDEX(History1!$A$2:$R$21,MATCH($A2,History1!$A$2:$A$21,0),MATCH(B$1,History1!$A$2:$R$2,0)) > Copy across to C2 > In D2 > > =SUM(INDEX(History1!$A$1:$Q$20,MATCH($A2,History1!$A$1:$A$20,0),MATCH(YEAR(B$1),History1!$A$1:$Q$1,0)): > INDEX(History1!$A$1:$Q$20,MATCH($A2,History1!$A$1:$A$20,0),MATCH(B$1,History1!$A$2:$Q$2,0))) > > in E2 > > =SUM(INDEX(History1!$A$1:$Q$20,MATCH($A2,History1!$A$1:$A$20,0),MATCH(YEAR($B$1)-1,History1!$A$1:$Q$1,0)): > INDEX(History1!$A$1:$Q$20,MATCH($A2,History1!$A$1:$A$20,0),MATCH($B$1,History1!$A$2:$Q$2,0)))-D2 > > In C1 of Sheet1 I put the formula > =Date(Year(B1),month(B1)-1,1) > and formatted the cell as mmm-yy > > As you change the Current Month in B2, so the values will alter. > -- > Regards > Roger Govier > > kathy wrote: > > Would your formula(or something like it) work for the problem I am having? > > End result is to compare prior year numbers to current year to date > > sheet 1 has the following columns-- sheet 2 has history-each month totals > > for a number of years > > > > COL A Secured Loan Interest > > COL B(current month -column title Mar-10) 39,333.98 > > COL C(prior month -column title Feb-10) $36,134.55 > > COL E(current YTD) $113,440.47 > > COL F(previous YTD ) $512,814.15 > > All of the information on worksheet 1 is filled in with a vlookup/match > > formula. ex. =VLOOKUP($A5,History1!$A$1:$CG$176,MATCH('Income > > Variance'!$C$3,History1!$A$1:$CG$1,0),0 > > I want COL F to add up the totals from sheet 2(called History1) for each > > month up to the current month for this year, so that this year and prior year > > totals are for > > the same timeframe. > > > > > . >
From: Roger Govier on 24 May 2010 17:45
Hi Kathy I was suggesting you used Index in place of Vlookup - but that is not important as fare as the existing Month and Previous Month are concerned (except they would need to alter, if we insert a new row on sheet History1 as I suggest, If you are not sure of what to do, send me a copy of your workbook, and I will set it up for you. To mail direct roger at technology4u dot co dot uk Replace at and dot and remove extra spaces, to create a vaild email address. -- Regards Roger Govier kathy wrote: > Not sure I follow you. On sheet one rows 5-100 have row titles ie. secured > loans,personal loan, etc. right now C5-100 and C5-100 have lookup > (=VLOOKUP($A5,History1!$A$2:$CG$177,MATCH('Income > Variance'!$B$3,History1!$A$2:$CG$2,0),0)which pulls the current months > numbers from the History1 tab. > > Are you saying that I replace my vlookup with the =index you sent for B2(or > B5 in my case) to pull current months numbers. Right now D5 has the > difference between last month and this month and E5 pulls the total for the > year with this lookup (=VLOOKUP($A5,History1!$A$2:$CG$177,MATCH('Income > Variance'!$E$3,History1!$A$2:$CG$2,0),0) > Column F is where I need the totals from prior year 2009 from Jan-current > month. > > Should I replace all the vlookups to the =index you sent? > Sorry if I am confusing you, but I have never used an =index before so amy > not positive I understand. > |