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

[rjheibel]

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.

 

[jasonb75]

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]

Jasonb75, Thanks for the suggestion. Bellow is the example I screenshotted above.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2			Calculating Cost per Category Code					Distribution of Cost over time
3		Resource ID	Cost Cost 1	% of cost code	Cost Cost 2	% of cost code	Total Cost	Jan	Feb	Mar	Apr
4		Category 1	22100	10%	33510	100%	$ 283,973.90	18%	7%	40%	35%
5		Category 2	22100	30%	22400	60%	$ 2,555,765.12	19%	5%	55%	21%
6		Category 3	22200	100%	22100	20%	$ 2,619,832.11	5%	32%	10%	53%
7		Category 4	22400	40%	42500	50%	$ 408,266.74	1%	0%	60%	39%
8		Category 5	22100	40%	56000	100%	$ 2,555,765.12	31%	12%	20%	37%
9
10
11
12
13
14			Desired Results based on Cost Code in column B
15		Cost Code	Jan	Feb	Mar	Apr		Calculation for cell C16
16		22100	21%	13%	31%	35%		=(D4*H4)+(D5*H5)+(D8*H6)+(F6*H8)
17		22200	29%	8%	10%	53%
18
Sheet1

Cell Formulas
Range	Formula
C16:F16	C16	=($D$4*H4)+($D$5*H5)+($D$8*H8)+($F$6*H6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:K8	Expression	=H3=0	text	NO

 

[jasonb75]

See if this looks right to you, I'm getting different results for part of the second row.

Book2 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1
2			Calculating Cost per Category Code					Distribution of Cost over time
3		Resource ID	Cost Cost 1	% of cost code	Cost Cost 2	% of cost code	Total Cost	Jan	Feb	Mar	Apr
4		Category 1	22100	10%	33510	100%	283973.902	18%	7%	40%	35%
5		Category 2	22100	30%	22400	60%	2555765.118	19%	5%	55%	21%
6		Category 3	22200	100%	22100	20%	2619832.11	5%	32%	10%	53%
7		Category 4	22400	40%	42500	50%	408266.744	1%	0%	60%	39%
8		Category 5	22100	40%	56000	100%	2555765.118	31%	12%	20%	37%
9
10
11
12
13
14			Desired Results based on Cost Code in column B
15		Cost Code	Jan	Feb	Mar	Apr
16		22100	21%	13%	31%	35%
17		22200	5%	32%	10%	53%
Sheet3

Cell Formulas
Range	Formula
C16:F17	C16	=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]

JasonB75, THANKS!! This seems to be working perfectly. Thanks for such a fast response.