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.
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.