Excel Help - Looking up a cell containing text I need to match and - Microsoft Community

	column a	column b	column c	column d
row 2		4%	7%	10%
3	option a	301	292	284
4	option b	196	180	168
5	option c	158	142	132
6	option d	422	353	309
7	option e	113	89	74
8
9	capital - a&b&d&e	301	460	677
10	o&m - a&b&d&e	422	101	395
11	total - a&b&d&e	723	561	1072

hello,

i'm not sure if i'm posting in correct place, here's start guess.

i wanting add (totalling in red text cell) , values in column b, rows 3-7, if options listed in column match in a9.

that is, i'd add values corresponding option a, option b, option d , option e included in a9

is possible?

thank you, , assistance appreciated.

cath

its possible in case data uniform or standardized ... single space in "option a", in cell a9 there "-" (dash followed letters inbetween "&", etc. refer below possible solution.

enter below formula in cell b9 & copy right:

=sumproduct(isnumber(search($a$3:$a$7,substitute("option "&right($a9,len($a9)-(find("-",$a9)+1)),"&"," option ")))*(b$3:b$7))

you may copy below row ie. row no 10 & formula remain valid.

regards,

amit tandon

www.globaliconnect.com

Office / Excel / Windows 8 / Office 2013