Sum of a variable range

[volkl77]

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

 

[Jaymond Flurrie]

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.

 

[Jaymond Flurrie]

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