How to change summing area dynamically ?

[Sir Vili]

Hi masters.

I have table in which I want to use Sumproduct function and Offset function.

Sumpro.xls
	A	B	C	D	E	F	G
1	7750	AYRLintl				Feb	Mar
2		Jan	Feb	Mar
3	AYRLintl	500	600	700		2	3
4	AYRLintl	400	450	500
5	AYRLintl	600	650	700		Jan	1
6	AYRLdom	100	200	300		Feb	2
7	AYRLdom	150	250	350		Mar	3
8	AYRLintl	800	900	950
Dynamic Summing

In A1 I am summing from B3:D24. I want that when selecting from from lists in F1 and G1 summing area changes for example from B3:D24 to C24:D24 (using Offset function). When selecting from list, I get numbers in F3 =VLOOKUP($F$1;$F$5:$G$7;2;FALSE) and G3 =VLOOKUP($G$1;$F$5:$G$7;2;FALSE).

I think, that using these numbers in F3 and G3(which are results from lists and from Vlookup function) can be used with Offset function to change summing area. But How to build formula?

So formula could be something:

=SUMPRODUCT((A3:A24=B1)*Offset(B3:D24 ......)

If there is another solution, I should be interested about that also.

Best regards Sir Vili.

 

[DonkeyOte]

I would say the easiest thing would be to set your offset to letters as opposed to column numbers...so Feb = C etc...

=INDIRECT("SUMPRODUCT((A3:A24=B1)*"&vlookup(f1,offset,2,false)&"3:D24")

if D is also to be variable then do the same there...

=INDIRECT("SUMPRODUCT((A3:A24=B1)*"&vlookup(f1,offset,2,false)&"3:"&vlookup(g1,offset,2,false)&"24")

Does this help

 

[Sir Vili]

Hello lasw10.

Thank You for Your reply. I did not understand it correcly and maybe that is why I could not get it work. I have now tried to solve this problem since yesterday and now just now I think I found solution.

Sumpro (version 2).xls
	A	B	C	D	E	F	G
1	4900	AYRLintl				Jan	Feb
2		Jan	Feb	Mar
3	AYRLintl	500	600	700		1	2
4	AYRLintl	400	450	500
5	AYRLintl	600	650	700		Jan	1
6	AYRLdom	100	200	300		Feb	2
7	AYRLdom	150	250	350		Mar	3
8	AYRLintl	800	900	950
Dynamic Summing

Now I only select month from F1 which is starting month and month from G1 which is ending month. In this way I can change summing area.

Best Regards Sir Vili.

 

[Aladin Akyurek]

On 2002-09-24 02:32, Sir Vili wrote:
Hi masters.

I have table in which I want to use Sumproduct function and Offset function.
[...]
In A1 I am summing from B3:D24. I want that when selecting from from lists in F1 and G1 summing area changes for example from B3:D24 to C24:D24 (using Offset function). When selecting from list, I get numbers in F3 =VLOOKUP($F$1;$F$5:$G$7;2;FALSE) and G3 =VLOOKUP($G$1;$F$5:$G$7;2;FALSE).

I think, that using these numbers in F3 and G3(which are results from lists and from Vlookup function) can be used with Offset function to change summing area. But How to build formula?

So formula could be something:

=SUMPRODUCT((A3:A24=B1)*Offset(B3:D24 ......)

If there is another solution, I should be interested about that also.

Best regards Sir Vili.

Name the data area DTable. In what follows I use a sample comparable to yours. DTable refers wrt this sample to A3:D8.

Let F1:H1 house "a", "Feb", and "Mar".

In F3 enter:

=SUMPRODUCT((A3:A8=F1)*(C3:D8))

In F4 enter:

=SUMPRODUCT((INDEX(DTable,0,1)=F1)*(INDEX(DTable,0,VLOOKUP(G1,J3:K5,2,0)+1):INDEX(DTable,0,VLOOKUP(H1,J3:K5,2,0)+1)))

which is I believe you're looking for.

See...

aaDynamicSumProduct SirVili.xls
	A	B	C	D	E	F	G	H	I	J	K
1						a	Feb	Mar
2	Item	Jan	Feb	Mar
3	a	500	600	700		4682				Jan	1
4	a	400	500	500		4682				Feb	2
5	a	200	240	400						Mar	3
6	b	600	350	250
7	a	400	540	356
8	a	300	346	500
Sheet1

 

[Sir Vili]

Thx Aladin.

I knew that You solve it. I just waited that You see my problem and solve it.

What Do You think my solution, which I found just before You answered to me. I was sure that solution can be done also by using Offset function, but I am little "confused" with Offset function.

Best Regards Sir Vili.

 

[Aladin Akyurek]

On 2002-09-24 07:51, Sir Vili wrote:
Thx Aladin.

I knew that You solve it. I just waited that You see my problem and solve it.

What Do You think my solution, which I found just before You answered to me. I was sure that solution can be done also by using Offset function, but I am little "confused" with Offset function.

Best Regards Sir Vili.

Yes, OFFSET can be unnerving. Although a solution with OFFSET is possible as you found out (congrats), it's a volatile function. I'd suggest switching to the INDEX formulation. INDEX is a darn fast accesssor function and, moreover, non-volatile.