Dynamic sum (summing all relevant data between two dates)

Rpan

New Member
Joined
Aug 7, 2002
Messages
12
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
ABCDEFGHIJKLMNO
1Inventory
2
3Jan-02creditSoldFeb-02creditSoldMar-02creditSoldApr-02creditSold..
4Shoes5035455540163453
5Pants100415852107521560412
6Shirts205218651335823
7Jackets30632783244816106
8Socks100425755156081545510
9
10
Sheet1

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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

warhorse927

New Member
Joined
May 18, 2002
Messages
30
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

New Member
Joined
Aug 7, 2002
Messages
12
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Aug 7, 2002
Messages
12
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
ABCDEFGHIJKL
1
2Inventory
31/1/02creditSold2/1/02creditSold3/1/02creditSold4/1/02
4Shoes50354555401634
5Pants100415852107521560
6Shirts2052186513358
7Jackets30632783244816
8Socks100425755156081545
9
10
11
12
13YTD/Credit
14DateRange1/1/029/1/02
15Shoes39
16Pants32
17Shirts26
Sheet1
 

Forum statistics

Threads
1,144,312
Messages
5,723,649
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top