Sumproduct puzzler - apologies for incorrect post to Programming

  • Thread starter Thread starter Bony Pony
  • Start date Start date
B

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