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