How to change summing area dynamically ?

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
Hi masters.

I have table in which I want to use Sumproduct function and Offset function.
Sumpro.xls
ABCDEFG
17750AYRLintlFebMar
2JanFebMar
3AYRLintl50060070023
4AYRLintl400450500
5AYRLintl600650700Jan1
6AYRLdom100200300Feb2
7AYRLdom150250350Mar3
8AYRLintl800900950
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Board Regular
Joined
Jul 11, 2002
Messages
148
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
ABCDEFG
14900AYRLintlJanFeb
2JanFebMar
3AYRLintl50060070012
4AYRLintl400450500
5AYRLintl600650700Jan1
6AYRLdom100200300Feb2
7AYRLdom150250350Mar3
8AYRLintl800900950
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCDEFGHIJK
1aFebMar
2ItemJanFebMar
3a5006007004682Jan1
4a4005005004682Feb2
5a200240400Mar3
6b600350250
7a400540356
8a300346500
Sheet1
 

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,329
Messages
5,723,737
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top