Searching two columns for one word

Hello

I need to look for a single word in two columns A and B which contain multiple words and then return that word with the totals of columns C and D. I've attached a sample book which should hopefully make it clearer.

Thanks in advance for help

Hi

Thanks for the reply, unfortunately it's returned 0 when I enter it and also when I copy it down...

This will work for your sample data:

=SUMIF($A$2:$A$5,"*"&$A14&"*",C$2:C$5)+SUMIF($B$2:$B$5,"*"&$A14&"*",C$2:C$5)

which can be copied across and down.

However, it will give misleading results if your keyword occurs in both columns for a particular row.

Hope this helps.

Pete

Thanks for your responses guys. Sorry to be a pain in the but for my main document it will have sometimes have the same results in both fields. Any ideas on how to get round that?

Also, I tried Ctrl+Shift+Enter and it's not working!

Cheers again

Steve

This array* formula seems to work:

=SUM(IF((ISNUMBER(SEARCH($A14,$A$2:$A$5)))+(ISNUMBER(SEARCH($A14,$B$2:$B$5))),C$2:C$5))

*An array formula needs to be committed using the key combination of <Ctrl><Shift><Enter> (CSE) instead of the usual <Enter>. If you do this correctly then Excel will wrap curly braces around the formula when viewed in the formula bar, but you should not type these yourself. If you need to amend the formula then you will have to use CSE again.

Hope this helps.

Pete

Beautiful! Cheers both of you for your help and patience!