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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,119
Office Version
  1. 365
  2. 2010
Please change your profile to reflect what version of Excel you're running.
If 365, I think this might work:

Code:
=SUM(INDEX(SORT(A1:B3),,2)*(C8:C10+D8:D10*(1+E8:E10/3^0.3)))
 

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
67
Office Version
  1. 365
Thanks for the reply. This formula doesn't quite do what I need. I think it requires the main list with all of the data to be sorted and have the same number of items in the list. That's two things I failed to mention in my original post. The main list will not necessarily be in any kind of alphanumeric order, and the main list (the bottom one in my post above) could have more entries than what the list with the values (the top list in my post above) may have. Sorry for not providing that info initially.

Any other ideas?

I am using office 365. I'll try to get that in my profile.

Thanks,
Mark
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,119
Office Version
  1. 365
  2. 2010
No, it sorts the data in place in the formula.

Book1
ABCDEFG
1B3
2C2
3A5
4
5
6
7
8A1021
9B532
10C243
11133.3996
Sheet1
Cell Formulas
RangeFormula
G11G11=SUM(INDEX(SORT(A1:B3),,2)*(C8:C10+D8:D10*(1+E8:E10/3^0.3)))


AND

Book1
ABCDEFG
1B3
2C2
3A5
4F2
5E10
6
7
8A1021
9B532
10C243
11E345
12F246
13393.7669
Sheet1
Cell Formulas
RangeFormula
G13G13=SUM(INDEX(SORT(A1:B5),,2)*(C8:C12+D8:D12*(1+E8:E12/3^0.3)))


OR you could look at the various cum sums:

Book1
ABCDEFGHIJK
1B3
2C2
3A5
4F2
5XX10
6
7
8A102167.192230967.19223
9B53236.9460157104.1382
10C24329.2613542133.3996
11E345 133.3996
12F24646.5227085179.9223
13XX234408.7821136.306771316.2291
Sheet1
Cell Formulas
RangeFormula
J8:J13J8=IFERROR(INDEX(B$1:B$5,MATCH(A8,A$1:A$5,0))*(C8+D8*(1+E8/3^0.3)),"")
K8:K13K8=SUM($J$8:J8)
G13G13=SUM(INDEX(SORT(A1:B5),,2)*(C8:C12+D8:D12*(1+E8:E12/3^0.3)))
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,119
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

Actually, this is the cum sum comparison. I guess you're right about the top table not having a valid entry for the bottom to use.

Book1
ABCDEFGHIJK
1B3
2C2
3A5
4F2
5XX10
6
7
8A102167.192230967.19223
9B53236.9460157104.1382
10C24329.2613542133.3996
11F34542.7689237176.1685
12XX246232.613542408.7821
13408.78210408.7821
Sheet1
Cell Formulas
RangeFormula
J8:J13J8=IFERROR(INDEX(B$1:B$5,MATCH(A8,A$1:A$5,0))*(C8+D8*(1+E8/3^0.3)),0)
K8:K13K8=SUM($J$8:J8)
G13G13=SUM(INDEX(SORT(A1:B5),,2)*(C8:C12+D8:D12*(1+E8:E12/3^0.3)))
 

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
67
Office Version
  1. 365
Here is what I am seeing with the original suggestion. G13 is the original sum. H13 has the formula you mentioned. I changed the main list to a random order. I added an item to the main list that does not show up in the upper list, and the formula doesn't calculate. I then copied everything lower in the table and added the missing item to the list with a 0 count, and you can see the results don't match. However, if I put the lower list in alphabetical order, the results match. I'm hoping to be able to have the lists in any order, however.

Book1
ABCDEFGH
1B1
2C4
3A4
4D0
5
6
7
8A102153.75378
9D5320
10C12329.26135
11B24314.63068
12
1397.64582#N/A
14
15
16B1
17C4
18A4
19D0
20
21
22
23A102153.75378
24D5320
25C12329.26135
26B24314.63068
27
2897.6458295.33048
Sheet1
Cell Formulas
RangeFormula
G8:G11,G23:G26G8=IF(ISNUMBER(MATCH(A8,A$1:A$4,0)),INDEX(B$1:B$4,MATCH(A8,A$1:A$4,0))*(C8+D8*(1+E8/3^0.3)),0)
G13,G28G13=SUM(G8:G11)
H13H13=SUM(INDEX(SORT(A1:B3),,2)*(C8:C11+D8:D11*(1+E8:E11/3^0.3)))
H28H28=SUM(INDEX(SORT(A16:B19),,2)*(C8:C11+D8:D11*(1+E8:E11/3^0.3)))



I'll take a quick look at your other posts in case you changed something that may get the formula doing what I'm hoping for. Again, thanks for the help!

Mark
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,119
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

Don't think I nailed it w/the other exceptions.

How about this adjustment?

Book1
ABCDEFGHIJK
1B3
2C2
3A5
4F2
5XX10
6
7
8A102167.192230967.19223
9B53236.9460157104.1382
10C24329.2613542133.3996
11F34542.7689237176.1685
12XX246232.613542408.7821
13E1230408.7821
14408.7821
Sheet1
Cell Formulas
RangeFormula
J8:J13J8=IFERROR(INDEX(B$1:B$5,MATCH(A8,A$1:A$5,0))*(C8+D8*(1+E8/3^0.3)),0)
K8:K13K8=SUM($J$8:J8)
G14G14=SUM(INDEX(SORT(A1:B6),,2)*(C8:C13+D8:D13*(1+E8:E13/3^0.3)))
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,119
Office Version
  1. 365
  2. 2010
Does everything in the 1st list (top) always appear in the 2nd (bottom) but maybe add'l entries in the bottom?

Maybe there is not straight-forward formula...might have to use VBA. Is that an option?
 

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
67
Office Version
  1. 365
The last suggestion still looks to not work in all scenarios. For instance, in the lower list, flip "B" and "E". You can leave the values alone and just put "E" in A9 and "B" in A13. For some reason the formula gets off track when that is done. Also, I would like to restrict the range in the sort to only those 5 rows with data. That list will be a fixed number of rows with other calcs and/or user input immediately below that list, and the main, lower list will be more than that number of rows.

To answer your last question, everything in the top list would always appear in the lower list.

I'm trying to avoid VBA. Before I do that, I might would just use individual rows with match/index to get the cumulatives and sum those up. I was just hoping there would be a one-stop formula to do it.

Thanks again! Don't give up!! : )

Mark
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,119
Office Version
  1. 365
  2. 2010

Forum statistics

Threads
1,144,612
Messages
5,725,315
Members
422,612
Latest member
Sanussha

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