# Sum of a variable range

#### volkl77

##### Board Regular
Hey all,

I am hoping someone can help me out. I am trying to figure out the easiest way to find the sum of a range of data based on a variable. Basically all my data is on one sheet with dates in row 2 (B2:LW2), departments in column A (A3:A36), and sales values for each department for each day (B3:LW36). On another sheet I have a list of dates that correspond to the last day of our pay weeks (B4:B29) Nov 4 for example. What i need to do is find the last day of the pay week for each department on the data sheet and then add the sales for that day and the previous 13 days (one pay period). Can someone suggest the best way to do this? Thanks

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hey all,

I am hoping someone can help me out. I am trying to figure out the easiest way to find the sum of a range of data based on a variable. Basically all my data is on one sheet with dates in row 2 (B2:LW2), departments in column A (A3:A36), and sales values for each department for each day (B3:LW36). On another sheet I have a list of dates that correspond to the last day of our pay weeks (B4:B29) Nov 4 for example. What i need to do is find the last day of the pay week for each department on the data sheet and then add the sales for that day and the previous 13 days (one pay period). Can someone suggest the best way to do this? Thanks

I'll give it a shot. First of all, calculate the number of columns for each ranges you want to sum. Either use Match function for it, or simply takes the multiples of 14, you end up with something like 1-14, 15-28... etc.

Find the first value of the range:
=OFFSET(Sheet1!\$A\$2;Sheet2!\$L\$2;Sheet2!B4)
where Sheet1!\$A\$2 is the upper left corner of the data area from where to sum (like you had it yourself), L2 is the row number of department and B4 is that first number of date order (1,15,29...)

Next the last value of the range:
=OFFSET(Sheet1!\$A\$2;Sheet2!\$L\$2;Sheet2!C4)
Same thing, but uses the last value of date order (14,28,42...)

And then sum of the range:
=SUM(OFFSET(Sheet1!\$A\$2;Sheet2!\$L\$2;Sheet2!B4):OFFSET(Sheet1!\$A\$2;Sheet2!\$L\$2;Sheet2!C4))

This Sheet2!\$L\$2 can be just
=MATCH(L1;Sheet1!\$A\$3:\$A\$4;0)
where the L1 is the department name and Sheet1!\$A\$3:\$A\$4 is the range of the rows (A3:A36 in your case). 0 means just exact match.

so you can "open" it to
=SUM(OFFSET(Sheet1!\$A\$2;MATCH(L1;Sheet1!\$A\$3:\$A\$4;0);Sheet2!B4):OFFSET(Sheet1!\$A\$2;MATCH(L1;Sheet1!\$A\$3:\$A\$4;0);Sheet2!C4))

Hopefully this helped you at least a bit from the beginning.

Last edited:
Just realized that you don't need to search for that last cell. Just use the optional 3rd and 4th parameter of OFFSET to define 1x14 size range. Like this:
=SUM(OFFSET(Sheet1!\$A\$2;MATCH(\$L\$1;Sheet1!\$A\$3:\$A\$4;0);Sheet2!B4;1;14))

Replies
3
Views
951
Replies
5
Views
251
Replies
4
Views
490
Replies
3
Views
436
Replies
1
Views
532

1,202,905
Messages
6,052,481
Members
444,586
Latest member
Godtymer

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