# Dynamic sum (summing all relevant data between two dates)

#### Rpan

##### New Member
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.

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.

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 ?

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)))

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

Replies
2
Views
184
Replies
6
Views
194
Replies
3
Views
166
Replies
3
Views
578
Replies
3
Views
230

1,219,829
Messages
6,150,475
Members
450,967
Latest member
itzwinger

### 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.

### Which adblocker are you using?

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

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