Sumproduct (maybe) help

[mArkcpp]

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
	A	B	C	D	E	F	G
1	B	3
2	C	2
3	A	5
4
5
6
7
8	A		10	2	1		67.19223
9	B		5	3	2		36.94602
10	C		2	4	3		29.26135
11
12							133.3996
Sheet1

Cell Formulas
Range	Formula
G8:G10	G8	=INDEX(B$1:B$3,MATCH(A8,A$1:A$3,0))*(C8+D8*(1+E8/3^0.3))
G12	G12	=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]

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

 

[kweaver]

Glad to have helped. LET is rather new and I'm still learning more and more about it's functionality.

 

[mArkcpp]

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
	A	B	C	D	E
1	B	3	1
2	C	2	2
3	A	5	1
4	XX	2	3
5	F	10	4
6		379.136251	195.706372
7
8	A		10	2	1
9	B		5	3	2
10	E		2	4	3
11	F		3	4	5
12	XX		2	4	6
13	C		1	2	3
Sheet2

Cell Formulas
Range	Formula
B6	B6	=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))))
C6	C6	=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]

I think I'd use this for C6 (and modify B6):

Book1
	A	B	C
1	B	3	1
2	C	2	2
3	A	5	1
4	XX	2	3
5	F	10	4
6		379.1363	195.7064
Sheet1

Cell Formulas
Range	Formula
B6	B6	=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))))
C6	C6	=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]

Another option for B6 dragged right, would be

=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)))

 

[kweaver]

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]

Thanks again kweaver! And thanks Fluff! That XLOOKUP version works perfectly. This message board never disappoints!

Mark

 

[Fluff]

Glad we could help & thanks for the feedback.