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

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/assistan...lconPT101.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

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/...9/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" wrote in message
...
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

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/assistan...lconPT101.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

"Matt's Dad" 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"))

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/...9/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" wrote in message
...
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

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.

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.


.

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.