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