COUNTIF and SUMIF with wildcards

[thekaoboy]

Suppose that I have the following data spread across several columns:

Red, Black
Green, White, Orange
Blue
Green, Orange
Yellow, Blue
Purple, Brown

I wanted to get a count of the number of items that contained the word "Blue", so I used the following formula:

=COUNTIF(G5:H7,"*"&F10&"*")
where G5:H7 contained the values from above and F10 contained the word "Blue".

I also have a column with numeric values, so I am looking to create a similar formula that sums these values based on the same criteria as the COUNTIF. I thought this formula would work:

=SUMIF(G5:H7,"*"&F10&"*",D5:D7)
where D5:D7 contains the numeric values

However, this only returns the first value it finds rather than the sum. I tried setting the formula as an array but I get the same result.

I have a feeling this is a simple one but I just can't figure it out.
Thanks for your help.

 

[Jonmo1]

Try

=SUMPRODUCT((ISNUMBER(SEARCH(F10,G5:H7)))*(D5:D7))

 

[thekaoboy]

Thank you! Much appreciated.