Formula to lookup and return a sum of products calculated from multiple columns based on specified value

rjheibel

New Member
Joined
Mar 8, 2018
Messages
17
I am trying to figure out a formula that will look through multiple columns for a specified value and return a sum of products if that value is found. Any help would be greatly appreciated.

In my example below, based on value in B16, I would like a formula in C16 to look through column C4:C8 and E4:E8 for this value. If found I would like the values in either D or F multiplied by the value in column H and summed together. The resulting formula of for the result I would like is shown below in H15. This is a small sample size of the actual data table.

1600453671375.png
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,448
Office Version
  1. 365
Platform
  1. Windows
It is not possible to test formulas on screen captures, I would suggest that you post your example using XL2BB so that people can copy it to excel and test their ideas (see link in my signature below).
 

rjheibel

New Member
Joined
Mar 8, 2018
Messages
17
Jasonb75, Thanks for the suggestion. Bellow is the example I screenshotted above.

Book1
ABCDEFGHIJKL
1
2Calculating Cost per Category CodeDistribution of Cost over time
3Resource IDCost Cost 1% of cost codeCost Cost 2% of cost codeTotal CostJanFebMarApr
4Category 12210010%33510100%$ 283,973.9018%7%40%35%
5Category 22210030%2240060%$ 2,555,765.1219%5%55%21%
6Category 322200100%2210020%$ 2,619,832.115%32%10%53%
7Category 42240040%4250050%$ 408,266.741%0%60%39%
8Category 52210040%56000100%$ 2,555,765.1231%12%20%37%
9
10
11
12
13
14Desired Results based on Cost Code in column B
15Cost CodeJanFebMarAprCalculation for cell C16
162210021%13%31%35%=(D4*H4)+(D5*H5)+(D8*H6)+(F6*H8)
172220029%8%10%53%
18
Sheet1
Cell Formulas
RangeFormula
C16:F16C16=($D$4*H4)+($D$5*H5)+($D$8*H8)+($F$6*H6)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H3:K8Expression=H3=0textNO
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,448
Office Version
  1. 365
Platform
  1. Windows
See if this looks right to you, I'm getting different results for part of the second row.
Book2 (version 1).xlsb
ABCDEFGHIJK
1
2Calculating Cost per Category CodeDistribution of Cost over time
3Resource IDCost Cost 1% of cost codeCost Cost 2% of cost codeTotal CostJanFebMarApr
4Category 12210010%33510100%283973.90218%7%40%35%
5Category 22210030%2240060%2555765.11819%5%55%21%
6Category 322200100%2210020%2619832.115%32%10%53%
7Category 42240040%4250050%408266.7441%0%60%39%
8Category 52210040%56000100%2555765.11831%12%20%37%
9
10
11
12
13
14Desired Results based on Cost Code in column B
15Cost CodeJanFebMarApr
162210021%13%31%35%
17222005%32%10%53%
Sheet3
Cell Formulas
RangeFormula
C16:F17C16=SUMPRODUCT(($C$4:$E$8=$B16)*$D$4:$F$8*INDEX($H$4:$K$8,0,MATCH(C$15,$H$3:$K$3,0)))
 

rjheibel

New Member
Joined
Mar 8, 2018
Messages
17
JasonB75, THANKS!! This seems to be working perfectly. Thanks for such a fast response.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,531
Messages
5,625,368
Members
416,096
Latest member
forevans

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