Dynamic sum (summing all relevant data between two dates)

[Rpan]

Hi guys - a dynamic sum question for you.

I have a table spreadsheet with inventory in Column A, and month columns with inventory on hand/credit/sold for each subsequent month.

I'm looking for a formula that will calculate the year to date credit.(essentially the simple formula for the credit amount on shoes in the HTML below would be D4+G4+J4+M4....)

However, the spreadsheet contained here has been simplified - the real sheet contains dates before Jan 02 and carries dates out until Jan 04, as well as additional columns in between each date.

As of now, I do the simple formula D4+G4+J4+M4... and have to update it every month.

Any advice welcome,
Renee

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Inventory
2
3			Jan-02	credit	Sold	Feb-02	credit	Sold	Mar-02	credit	Sold	Apr-02	credit	Sold	..
4	Shoes		50	3	5	45	5	5	40	1	6	34	5	3
5	Pants		100	4	15	85	2	10	75	2	15	60	4	12
6	Shirts		20	5	2	18	6	5	13	3	5	8	2	3
7	Jackets		30	6	3	27	8	3	24	4	8	16	10	6
8	Socks		100	4	25	75	5	15	60	8	15	45	5	10
9
10
Sheet1

This message was edited by Rpan on 2002-09-10 19:45

 

[PaddyD]

without looking at the details of your problem, might this help:

http://mrexcel.com/board/viewtopic.php?topic=12429&forum=2

It does some rolling & YTD calculations. See what you make of it & post back...

Paddy

 

[warhorse927]

Just a thought, I did something like this a while back and, organizationally, I put each month on its own worksheet (12) but I had a 13th sheet that was summary data only. Paddy's formula looks to be what you need, you could put the YTD on each sheet and the summary to give yourself a 12 month history.

 

[Rpan]

To PaddyD's reply - the months columns are not contiguous arrays, that is the data (Credit data in this case) is always one column right of the month it corresponds to, separated by additional columns in between the next month's data

To warhorse927's reply - I have 10 sheets that are similar in nature and most require this YTD calc as well as needing to reside on their respective "origination" sheets

Is there a SUM(OFFSET(MATCH formula ?

 

[Aladin Akyurek]

On 2002-09-10 20:06, Rpan wrote:
To PaddyD's reply - the months columns are not contiguous arrays, that is the data (Credit data in this case) is always one column right of the month it corresponds to, separated by additional columns in between the next month's data

To warhorse927's reply - I have 10 sheets that are similar in nature and most require this YTD calc as well as needing to reside on their respective "origination" sheets

Is there a SUM(OFFSET(MATCH formula ?

=SUMPRODUCT((MOD(COLUMN(C4:N4)-CELL("Col",C4)+2,3)=0)*(C4:N4))

This formula computes what your =D4+G4+J4+M4 is computing.

What you want is of course to replace

C4:N4

by a dynamically defined range. This requires using OFFSET combined with MATCH:

Let B1 house 04/01/2002.

In C1 enter:

=MATCH(B1,3:3,0)+2

Where 2 is the # columns of interest after the date entry in your data.

and using the result of the above in an OFFSET bit that you use in the SUMPRODUCT formula instead of C4:N4...

=SUMPRODUCT((MOD(COLUMN(OFFSET(C4,0,0,1,C1+2-1))-CELL("Col",C4)+2,3)=0)*(OFFSET(C4,0,0,1,C1+2-1)))

Aladin

 

[Rpan]

Thanks Aladin - your post was just the answer I was hoping to find - I modified slightly for my sheet's needs - placing in a DATE(YEAR(TODAY()),1,1) formula and current month DATE formula so that it will roll forward next year.

For anyone interested - the HTML is below.

01 - Allocations.xls delete good.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Inventory
3			1/1/02	credit	Sold	2/1/02	credit	Sold	3/1/02	credit	Sold	4/1/02
4	Shoes		50	3	5	45	5	5	40	1	6	34
5	Pants		100	4	15	85	2	10	75	2	15	60
6	Shirts		20	5	2	18	6	5	13	3	5	8
7	Jackets		30	6	3	27	8	3	24	4	8	16
8	Socks		100	4	25	75	5	15	60	8	15	45
9
10
11
12
13	YTD/Credit
14	DateRange	1/1/02	9/1/02
15	Shoes	39
16	Pants	32
17	Shirts	26
Sheet1