Excel XP Sum a moving range 1

[vaneagle]

Hi,

I am not sure if this is possible. Is it possible to sum a range depending on a certain criteria.

Data
Col A Col B Col C Col D Col E Col F Col G
Store Last WK1 WK2 WK3 WK4 WK5
123 1 600 560 450 760 453
456 2 300 340 270 370 260
789 4 200 220 250 300 210

So:

for store 123 the total would be = 2823 sum(C3:G3)
for store 456 the totl would be = 1240 sum(D4:G3)
for store 789 the totl would be = 510 sum(F4:G3)

Is there anyway that you can get the first criteria of the sum formula to be flexible? Ie I don't want to manually sum each store for the range of last to current week. So for store 123 where the sum function refers to C3 and G3 is it possible for this to be flexible? I am not talking relative referencing either ie.. $ sign in the formula...

so if i change cell b3 to a 4 the new total for store 123 would be 1213.

Any ideas?

 

[macropod]

Hi Vaneagle

You can do this with a combined SUm & OFFSET formula. I've restructured your data slightly for greater flexibility, which you may/may not need, as follows:

Col A    Col B  Col C   Col D  Col E    Col F   Col G   Col H   Col I
Store    Last    First    Total    WK1     WK2    WK3    WK4    WK5
123        1       1               600     560    450    760     453
456        2       5               300     340    270    370     260
789        4       4               200     220    250    300     210

The restructure involves the addition of a 'First' Column and a 'Total' column ahead of the WK data columns.

Now, in D2, type: =SUM(OFFSET(D2,,B2):OFFSET(D2,,C2))
and copy down as far as needed. What this allows you to do is to change both the first and last WK data columns that will be summed by the simple expedient of changing the values in the First and Last columns.

Cheers

 

[vaneagle]

That is great!!

Have a star!!

Quick question: How did you get the data in your post to look lke that I had to type it all in!! Yours looks great and makes it easy to understand and read!!

or did you just surround the text with the code tags?

either way thank you very much... I see many possibilities with this combined function... I have used offset before but not with sum...

vaneagle

 

[SkipVought]

Take a look at Process TGML hyperlink below the Your Reply window.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vaneagle]

Hi Skip,

Thanks skip... I shall have a look.....