Sumproduct puzzler - apologies for incorrect post to Programming

[Bony Pony]

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

 

[Pete_UK]

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

 

[Bony Pony]

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]

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

 

[Max]

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

 

[Pete_UK]

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

 

[Roger Govier]

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.

 

[Bony Pony]

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

 

[Bony Pony]

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

 

[Bony Pony]

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

 

[Pete_UK]

You're welcome.

Pete

 

[Max]

Welcome, Bony
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik