Sumproduct (maybe) help

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
67
Office Version
  1. 365
Hello everyone. It's been quite some time since I've posted a question here, but y'all have certainly helped me out in the past. I have a list of IDs with a count next to each ID that I need to be able to multiply that count by a series of values associated with each individual ID. I am trying to use sumproduct, but the list of IDs are not in the same order between where the counts are specified and where the series of values for each ID are. Here is a snapshot of what I'm trying to accomplish:

Book1
ABCDEFG
1B3
2C2
3A5
4
5
6
7
8A102167.19223
9B53236.94602
10C24329.26135
11
12133.3996
Sheet1
Cell Formulas
RangeFormula
G8:G10G8=INDEX(B$1:B$3,MATCH(A8,A$1:A$3,0))*(C8+D8*(1+E8/3^0.3))
G12G12=SUM(G8:G10)


Ultimately I'm looking for a single formula to give me the result in G12 without having to calculate the value for each ID first. I'm not sure if this can be done with sumproduct or if it is a combination of sumproduct and simif or match/index inside a sumproduct. I'm striking out with my internet searches and trying various configurations of different formulas. If the IDs were in order between the two lists, it would be simpler, but the lists may be in a different order, so I'm trying to accomodate that.

Any help is greatly appreciated.

Thanks,
Mark
 

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
67
Office Version
  1. 365
Looks like this does the trick. I'm not sure I understand how though!! It feels like that formula is almost acting as a do loop where it gets each individual value from the top list, multiplies it to the result of the formula using the lower list values, and sums up all the results. I wouldn't have ever come up with this one.

Thanks for the help!

Mark
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,986
Office Version
  1. 365
  2. 2010
Glad to have helped. LET is rather new and I'm still learning more and more about it's functionality.
 

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
67
Office Version
  1. 365
Found one more thing to mention. The spreadsheet will be set up where all columns to the right will have the values/counts for the different IDs. I can still use the formula you provided with a slight modification to continuously increase the range and column ID. See below:

Book1
ABCDE
1B31
2C22
3A51
4XX23
5F104
6379.136251195.706372
7
8A1021
9B532
10E243
11F345
12XX246
13C123
Sheet2
Cell Formulas
RangeFormula
B6B6=SUM(LET(val,IFERROR(VLOOKUP($A$8:$A$13,$A$1:B$5,2,FALSE),0),val*($C$8:$C$13+$D$8:$D$13*(1+$E$8:$E$13/3^0.3))))
C6C6=SUM(LET(val,IFERROR(VLOOKUP($A$8:$A$13,$A$1:C$5,COLUMNS($A$1:C$5),FALSE),0),val*($C$8:$C$13+$D$8:$D$13*(1+$E$8:$E$13/3^0.3))))


I'm just wondering if there is a better way where the range doesn't have to include preceding columns. The formula would be in every column, and every column is standalone. There would never be a need for the formula in C6 to need to use the values in B1:B5. I don't think how I have it above will ever cause me any trouble....just wondering if there is a way to only include the values in the column that the formula will live in. The IDs would only show up once in column A. Hope that makes sense.

Thanks again. I'll definitely have to look into the LET formula. Seems like it has potential to do some impressive things.

Mark
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,986
Office Version
  1. 365
  2. 2010
I think I'd use this for C6 (and modify B6):

Book1
ABC
1B31
2C22
3A51
4XX23
5F104
6379.1363195.7064
Sheet1
Cell Formulas
RangeFormula
B6B6=SUM(LET(val,IFERROR(VLOOKUP($A8:$A13,$A$1:B$5,2,FALSE),0),val*($C$8:$C$13+$D$8:$D$13*(1+$E$8:$E$13/3^0.3))))
C6C6=SUM(LET(val,IFERROR(VLOOKUP($A8:$A13,$A$1:C$5,3,FALSE),0),val*($C$8:$C$13+$D$8:$D$13*(1+$E$8:$E$13/3^0.3))))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,161
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option for B6 dragged right, would be
Excel Formula:
=SUM(XLOOKUP($A$8:$A$13,$A$1:$A$5,B$1:B$5,0,0)*($C$8:$C$13+$D$8:$D$13*(1+$E$8:$E$13/3^0.3)))
 
Solution

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,986
Office Version
  1. 365
  2. 2010
I keep forgetting about XLOOKUP...maybe it's because I'm trying to get a better handle on LET. Fluff, you're always on top of it!!

I better start saying "if it's VLOOKUP, try XLOOKUP."
 

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
67
Office Version
  1. 365
Thanks again kweaver! And thanks Fluff! That XLOOKUP version works perfectly. This message board never disappoints!

Mark
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,161
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,708
Messages
5,654,854
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top