How to create a dynamic sum formula?

[Mark McInerney]

Hi All,

I have weekly sales data stored, and have a week reference number from 1 to 52 across the top.

Is there a formula that will allow me to sum from week reference 2 to 7, or 3 to 13, or any combination that I choose?

Thanks for any help - Appreciated.

Best - Mark.

 

[Marcelo Branco]

Say you have:
the week reference numbers 1 to 52 in E1:BD1
the weekly sales in E2:BD2

Put 2 (begin week) in A2 and 7 (end week) in B2

C2
=SUM(INDEX($E2:$BD2,MATCH($A2,$E$1:$BD$1)):INDEX($E2:$BD2,MATCH($B2,$E$1:$BD$1)))

Adjust the ranges accordingly

Hope this helps

M.

 

[Mark McInerney]

Brilliant...Just Brilliant! - Many Thanks Marcelo. Really Appreciate it.

 

[Mark McInerney]

Hi Marcelo,

Just wondering if it can be hooked up with a vlookup / hlookup option. That is working fine - really cool. If I have 3 different sales departments with data from E2:BD5 and I want to select one of these rows of data from a drop down box and then run my dynamic sum as you have described above - is this possible?

 

[Marcelo Branco]

A data sample with just 12 weeks


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Begin​

[/TD]
[TD]

End​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

7​

[/TD]
[TD]

114​

[/TD]
[TD]

DEPT1​

[/TD]
[TD]

10​

[/TD]
[TD]

12​

[/TD]
[TD]

14​

[/TD]
[TD]

16​

[/TD]
[TD]

18​

[/TD]
[TD]

20​

[/TD]
[TD]

22​

[/TD]
[TD]

24​

[/TD]
[TD]

26​

[/TD]
[TD]

28​

[/TD]
[TD]

30​

[/TD]
[TD]

32​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

DEPT2​

[/TD]
[TD]

12​

[/TD]
[TD]

14​

[/TD]
[TD]

16​

[/TD]
[TD]

18​

[/TD]
[TD]

20​

[/TD]
[TD]

22​

[/TD]
[TD]

24​

[/TD]
[TD]

26​

[/TD]
[TD]

28​

[/TD]
[TD]

30​

[/TD]
[TD]

32​

[/TD]
[TD]

34​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

DEPT​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

DEPT3​

[/TD]
[TD]

14​

[/TD]
[TD]

16​

[/TD]
[TD]

18​

[/TD]
[TD]

20​

[/TD]
[TD]

22​

[/TD]
[TD]

24​

[/TD]
[TD]

26​

[/TD]
[TD]

28​

[/TD]
[TD]

30​

[/TD]
[TD]

32​

[/TD]
[TD]

34​

[/TD]
[TD]

36​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

DEPT2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Put in A2, B2 and A5 the search values

Formula in C2
=SUM(INDEX(E:BD,MATCH(A5,D:D,0),MATCH($A2,$E$1:$BD$1)):INDEX(E:BD,MATCH(A5,D:D,0),MATCH($B2,$E$1:$BD$1)))

Hope this helps

M.

 

[Mark McInerney]

Have not even tried it yet...but I know it will work based on your last one! Really Big Thanks - Genuinely appreciated.

Huge Help - Thank You!

Best - Mark.

 

[Marcelo Branco]

Have not even tried it yet...but I know it will work based on your last one! Really Big Thanks - Genuinely appreciated.

Huge Help - Thank You!

Best - Mark.

Mark

You are very welcome. Thanks for your kind words.

M.

 

[L. Howard]

Hi Mark,

This seems to work for me.
I defined a name for each row of sales. E to BD.

eTwo, eThree, eFour eFive as an example of each department name. (And row)

I put those names in a drop down in C1

With the week start number in A2, and the week end number in B2, then select the Department (name) in C1 from the drop down.

=SUM(INDEX(INDIRECT(C1),MATCH($A2,$E$1:$BD$1)):INDEX(INDIRECT(C1),MATCH($B2,$E$1:$BD$1)))

Regards,
Howard

 

[Marcelo Branco]

Nice! Good job.

M.

 

[L. Howard]

Thanks, Marcelo. I probably got the idea from you some time ago.<g>

Howard