# How to change summing area dynamically ?

#### Sir Vili

##### Board Regular
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

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.

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...
ABCDEFGHIJK
1aFebMar
2ItemJanFebMar
3a5006007004682Jan1
4a4005005004682Feb2
5a200240400Mar3
6b600350250
7a400540356
8a300346500
Sheet1

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.

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

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.

Replies
1
Views
256
Replies
3
Views
194
Replies
4
Views
534
Replies
1
Views
215
Replies
1
Views
308

1,219,035
Messages
6,145,906
Members
450,654
Latest member
lorento

### 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?

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