July 11, 2017

Hi There,

Each week I need to total the last 20 weeks for each row on the attached, I am using a sum/index formula which works fine but I have to change the formula and drag down every week.

I'm trying to create a sum/index/match formula where all I would need to do is change the week numbers in cells BL1 & BL2 which would eradicate the need to drag formula down.

You can see my attempt in cell BL3, just can not get it to work

Any help gratefully accepted

Thanks

Paul

VIP

Trusted Members

December 7, 2016

Hi Paul,

Not sure why you use the INDEX function, I don't see any need of it as one and each of the site names are unique.

Anyway, I simplified your formula, as can be seen in cell BO3 in attached file.

In cell BQ3 you will see one solution to get a dynamic approach, you can use other solutions too (non-alcoholic I mean). 😉

The formula you see in cell BQ3 is as below. Do take in mind that in Sweden the semi-colon (;) is the divider whereas comma (,) is the divider for other countries, so the formula below contains the Swedish. When you open the attached file you will see the correct divider as per your regional settings.

=SUM(INDIRECT(ADDRESS(ROW();MATCH($BL$1;$2:$2;0);3;1);1):INDIRECT(ADDRESS(ROW();MATCH($BM$1;$2:$2;0);3;1);1))*100

I don't know if you need any explanation, but I try to write some anyway.

In order to get a dynamic approach, I need to get the cell reference correct for each rows and columns based on what week numbers you are interested in, for that I use the ADDRESS function. The row number is taken from the current row using ROW() function, the column number is taken from the MATCH results from cells BL1 and BM1, where I look in row 2 for a direct match. As the ADDRESS function returns a text value I need to use the INDIRECT function to transform it to valid cell references.

All you have to do now is to write start and end weeks in cells BL1 and BM1 and the calculations in BQ column will dynamically update accordingly.

I hope this helps you get through your obstacle.

Br,

Anders

Answers Post

VIP

Trusted Members

June 25, 2016

Hi Paul

Another formula (there can also be many other methods) is

**=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),MATCH($BL$1,$2:$2,0))),0,0,1,20))*100**

Just copy it to any column in row 3. You only need to specify the start week in cell BL1, no need for the end week.

The formula will sum 20 weeks. If you need to change the number of weeks, just change the number in **red **in the formula.

You can also put this number into a cell and refer from it if you want.

I am also using the MATCH function to get the starting week number cell address.

I then use the OFFSET function to sum the number of columns required (20 in this case) beginning from the starting week column.

Cheers

Sunny

VIP

April 21, 2015

VIP

Trusted Members

December 7, 2016

Hi Paul,

Glad you found the solutions useful. Even though we use functions that are volatile they are useful and don't make any fuzz in a file with small data loads. But if you by chance get a growing file and start notice longer response times, then it's a good time to see if you can minimize or perhaps even remove the usage of these INDIRECT and OFFSET functions.

Br,

Anders

VIP

Trusted Members

June 25, 2016

Hi Paul

Having thousands of formulas using INDIRECT and OFFSETS will definitely slow the calculation to a crawl.

Both these functions are volatile and will recalculate after every action as you have discovered.

In your situation, you should consider looking for other alternatives.

You can see if my example. It uses SUMIFS.

You only need to enter the start week and it will sum 20 weeks for you.

If you still need to use these functions e.g.

=SUM(INDIRECT(ADDRESS(ROW();**MATCH($BL$1;$2:$2;0)**;3;1);1):INDIRECT(ADDRESS(ROW();**MATCH($BM$1;$2:$2;0)**;3;1);1))*100

then maybe you could try putting both the MATCH functions in a cell and refer to them from the formula.

It will only need to calculate once instead of thousand of times if you copy down the original formula.

Hope this helps.

Sunny

1 Guest(s)