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