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 -