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

#### rjheibel

##### New Member
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.

### 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
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
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
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
JasonB75, THANKS!! This seems to be working perfectly. Thanks for such a fast response.

Replies
1
Views
59
Replies
11
Views
129
Replies
8
Views
104
Replies
0
Views
379
Replies
1
Views
157

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.

### Which adblocker are you using?

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

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