Sumproduct puzzler
 

Hello Lords of Excel!

I know this can be solved in one cell but am going round in circles.

I have 20 Volume Descriptions with varying volumes over 160 months.

Further down on the shet, I have a user selectable range of these Volumes of
up to 5 catagories.

What I can do over two ranges is index match the volume to fit the selection
by row no problem. I can then sum those 5 cells in the column and create
another range which states each row as a % of the subset.

example:
A B C D E
1 Mth 1 Mth 2 Mth 3 Mth 4 etc to Mth 160
2 Vol A 100
3 Vol B 105
4 Vol C 110
5 Vol D 115
6 Vol E 120
7 Vol F 125
.... etc
20 Vol T 60

User Selects:

25 Vol B Index Match returns 105
26 Vol E 120
27 Vol F 125
28 Vol T 60

Next Range
35 Vol B % of Selected Volumes = 25% (105/sum(105,120,125,60))
36 Vol E % of Selected Volumes = 29%
37 Vol F % = 30%
38 Vol T% = 14%

I want the % to be calc'd in rows 25 - 28 as the Vols are selected by Row.
Can this be done?

Thank you so much for taking the time to read this far!

best regards,
Bony

Sumproduct puzzler
 

=VLOOKUP(A35,$A$25:$B$28,2,FALSE)/SUM($B$25:$B$28)

--
__________________________________
HTH

Bob

"Bony Pony" wrote in message
...
Hello Lords of Excel!

I know this can be solved in one cell but am going round in circles.

I have 20 Volume Descriptions with varying volumes over 160 months.

Further down on the shet, I have a user selectable range of these Volumes
of
up to 5 catagories.

What I can do over two ranges is index match the volume to fit the
selection
by row no problem. I can then sum those 5 cells in the column and create
another range which states each row as a % of the subset.

example:
A B C D E
1 Mth 1 Mth 2 Mth 3 Mth 4 etc to Mth 160
2 Vol A 100
3 Vol B 105
4 Vol C 110
5 Vol D 115
6 Vol E 120
7 Vol F 125
... etc
20 Vol T 60

User Selects:

25 Vol B Index Match returns 105
26 Vol E 120
27 Vol F 125
28 Vol T 60

Next Range
35 Vol B % of Selected Volumes = 25% (105/sum(105,120,125,60))
36 Vol E % of Selected Volumes = 29%
37 Vol F % = 30%
38 Vol T% = 14%

I want the % to be calc'd in rows 25 - 28 as the Vols are selected by Row.
Can this be done?

Thank you so much for taking the time to read this far!

best regards,
Bony

Sumproduct puzzler
 

Many thanks Bob.

"Bob Phillips" wrote:

=VLOOKUP(A35,$A$25:$B$28,2,FALSE)/SUM($B$25:$B$28)

--
__________________________________
HTH

Bob

"Bony Pony" wrote in message
...
Hello Lords of Excel!

I know this can be solved in one cell but am going round in circles.

I have 20 Volume Descriptions with varying volumes over 160 months.

Further down on the shet, I have a user selectable range of these Volumes
of
up to 5 catagories.

What I can do over two ranges is index match the volume to fit the
selection
by row no problem. I can then sum those 5 cells in the column and create
another range which states each row as a % of the subset.

example:
A B C D E
1 Mth 1 Mth 2 Mth 3 Mth 4 etc to Mth 160
2 Vol A 100
3 Vol B 105
4 Vol C 110
5 Vol D 115
6 Vol E 120
7 Vol F 125
... etc
20 Vol T 60

User Selects:

25 Vol B Index Match returns 105
26 Vol E 120
27 Vol F 125
28 Vol T 60

Next Range
35 Vol B % of Selected Volumes = 25% (105/sum(105,120,125,60))
36 Vol E % of Selected Volumes = 29%
37 Vol F % = 30%
38 Vol T% = 14%

I want the % to be calc'd in rows 25 - 28 as the Vols are selected by Row.
Can this be done?

Thank you so much for taking the time to read this far!

best regards,
Bony