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

I'm not sure why you think you need SUMPRODUCT for this - isn't it
just:

=B25/SUM(B$25:B$28)

in B35, formatted as a percentage and then copied down to B38?

Hope this helps.

Pete

On Dec 15, 10:32*am, Bony Pony
wrote:
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

Hi Pete,
No not really. I know and said in my text that that is how I would do it if
I was to do it in two individual steps. I want to do it in a single step.

Thanks anyway.

Bony

"Pete_UK" wrote:

I'm not sure why you think you need SUMPRODUCT for this - isn't it
just:

=B25/SUM(B$25:B$28)

in B35, formatted as a percentage and then copied down to B38?

Hope this helps.

Pete

On Dec 15, 10:32 am, Bony Pony
wrote:
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

Okay, put this formula in B25:

=IF(A25="","",SUMPRODUCT((A25=A$2:A$21)*(B$2:B$21) )/SUMPRODUCT(((A$25=A
$2:A$21)+(A$26=A$2:A$21)+(A$27=A$2:A$21)+(A$28=A$2 :A$21)+(A$29=A$2:A
$21))*(B$2:B$21)))

Format as percentage, and then copy it down to B29.

Hope this helps.

Pete


On Dec 15, 11:07*am, Bony Pony
wrote:
Hi Pete,
No not really. *I know and said in my text that that is how I would do it if
I was to do it in two individual steps. *I want to do it in a single step.

Thanks anyway.

Bony



"Pete_UK" wrote:
I'm not sure why you think you need SUMPRODUCT for this - isn't it
just:

=B25/SUM(B$25:B$28)

in B35, formatted as a percentage and then copied down to B38?

Hope this helps.

Pete

On Dec 15, 10:32 am, Bony Pony
wrote:
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- Hide quoted text -

- Show quoted text -

Actually, if you want to copy it across to pick up your other months,
then you will need to make all those references to column A into
absolute columns, like this:

=IF($A25="","",SUMPRODUCT(($A25=$A$2:$A$21)*(B$2:B $21))/SUMPRODUCT((($A
$25=$A$2:$A$21)+($A$26=$A$2:$A$21)+($A$27=$A$2:$A$ 21)+($A$28=$A$2:$A
$21)+($A$29=$A$2:$A$21))*(B$2:B$21)))

Then it will calculate for each column in turn.

Hope this helps.

Pete


On Dec 15, 12:48*pm, Pete_UK wrote:
Okay, put this formula in B25:

=IF(A25="","",SUMPRODUCT((A25=A$2:A$21)*(B$2:B$21) )/SUMPRODUCT(((A$25=A
$2:A$21)+(A$26=A$2:A$21)+(A$27=A$2:A$21)+(A$28=A$2 :A$21)+(A$29=A$2:A
$21))*(B$2:B$21)))

Format as percentage, and then copy it down to B29.

Hope this helps.

Pete

On Dec 15, 11:07*am, Bony Pony
wrote:



Hi Pete,
No not really. *I know and said in my text that that is how I would do it if
I was to do it in two individual steps. *I want to do it in a single step.

Thanks anyway.

Bony

"Pete_UK" wrote:
I'm not sure why you think you need SUMPRODUCT for this - isn't it
just:

=B25/SUM(B$25:B$28)

in B35, formatted as a percentage and then copied down to B38?

Hope this helps.

Pete

On Dec 15, 10:32 am, Bony Pony
wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Pete,
Excellent solution! Many thanks! I went with Max's because it has fewer
keystrokes ...

Thank you for your time and help.

Kind regards,
Bony

"Pete_UK" wrote:

Actually, if you want to copy it across to pick up your other months,
then you will need to make all those references to column A into
absolute columns, like this:

=IF($A25="","",SUMPRODUCT(($A25=$A$2:$A$21)*(B$2:B $21))/SUMPRODUCT((($A
$25=$A$2:$A$21)+($A$26=$A$2:$A$21)+($A$27=$A$2:$A$ 21)+($A$28=$A$2:$A
$21)+($A$29=$A$2:$A$21))*(B$2:B$21)))

Then it will calculate for each column in turn.

Hope this helps.

Pete


On Dec 15, 12:48 pm, Pete_UK wrote:
Okay, put this formula in B25:

=IF(A25="","",SUMPRODUCT((A25=A$2:A$21)*(B$2:B$21) )/SUMPRODUCT(((A$25=A
$2:A$21)+(A$26=A$2:A$21)+(A$27=A$2:A$21)+(A$28=A$2 :A$21)+(A$29=A$2:A
$21))*(B$2:B$21)))

Format as percentage, and then copy it down to B29.

Hope this helps.

Pete

On Dec 15, 11:07 am, Bony Pony
wrote:



Hi Pete,
No not really. I know and said in my text that that is how I would do it if
I was to do it in two individual steps. I want to do it in a single step.

Thanks anyway.

Bony

"Pete_UK" wrote:
I'm not sure why you think you need SUMPRODUCT for this - isn't it
just:

=B25/SUM(B$25:B$28)

in B35, formatted as a percentage and then copied down to B38?

Hope this helps.

Pete

On Dec 15, 10:32 am, Bony Pony
wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

.. I want to do it in a single step

In B25:
=IF(A25="","",INDEX(B$2:B$20,MATCH(A25,A$2:A$20,0) )/SUMPRODUCT(--(ISNUMBER(MATCH(A$2:A$20,A$25:A$28,0))),B$2:B$20))
Format B25 as percentage, copy down to B28
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

Hi Max,
Thank you very very much! You really helped me out!
Kind regards,
Bony

"Max" wrote:

.. I want to do it in a single step

In B25:
=IF(A25="","",INDEX(B$2:B$20,MATCH(A25,A$2:A$20,0) )/SUMPRODUCT(--(ISNUMBER(MATCH(A$2:A$20,A$25:A$28,0))),B$2:B$20))
Format B25 as percentage, copy down to B28
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

Welcome, Bony
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Bony Pony" wrote in message
...
Hi Max,
Thank you very very much! You really helped me out!
Kind regards,
Bony

Hi

One way would be the array entered formula
{=INDEX(B$2:B$20,MATCH(A25,$A$2:$A$20,0))/
SUMPRODUCT(($A$2:$A$20=TRANSPOSE($A$25:$A$28))
*$B$2:$B$20)}

To enter or amend an array formula, use Control+Shift+Enter (CSE) not just
Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel will
insert the braces for you.

--
Regards
Roger Govier

"Bony Pony" wrote in message
...
Hi Pete,
No not really. I know and said in my text that that is how I would do it
if
I was to do it in two individual steps. I want to do it in a single step.

Thanks anyway.

Bony

"Pete_UK" wrote:

I'm not sure why you think you need SUMPRODUCT for this - isn't it
just:

=B25/SUM(B$25:B$28)

in B35, formatted as a percentage and then copied down to B38?

Hope this helps.

Pete

On Dec 15, 10:32 am, Bony Pony
wrote:
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

Hi Roger,
Many thanks for your help and time on this.

I prefer to steer clear from Arrays wherever possible which is why I wanted
to go down the sumproduct route.

Kind regards,
Bony

"Roger Govier" wrote:

Hi

One way would be the array entered formula
{=INDEX(B$2:B$20,MATCH(A25,$A$2:$A$20,0))/
SUMPRODUCT(($A$2:$A$20=TRANSPOSE($A$25:$A$28))
*$B$2:$B$20)}

To enter or amend an array formula, use Control+Shift+Enter (CSE) not just
Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel will
insert the braces for you.

--
Regards
Roger Govier

"Bony Pony" wrote in message
...
Hi Pete,
No not really. I know and said in my text that that is how I would do it
if
I was to do it in two individual steps. I want to do it in a single step.

Thanks anyway.

Bony

"Pete_UK" wrote:

I'm not sure why you think you need SUMPRODUCT for this - isn't it
just:

=B25/SUM(B$25:B$28)

in B35, formatted as a percentage and then copied down to B38?

Hope this helps.

Pete

On Dec 15, 10:32 am, Bony Pony
wrote:
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