SUMIF, looking for string values within an array

[TomH01]

I need to sum a range if a string is contained within an array of values.

	A	B	C
1	Critieria	Range	Sum Range
2	Product1	Half Product1	40
3	Product2	Product1	250
4	Product3	Some Product2	34
5		Product3	42
6		All Product3	50

<tbody>
</tbody>

In this instance I'd want to sum the whole range, as the critiera is contained within each of the cells. I've tried using SUMIFS but that only works on exact matching the criteria to the range.

Any help would be much appreciated.

 

[Andrew Poulsom]

You can use wildcards (* and ?) in SUMIF(S):

Excel 2010
	A	B	C	D
1	Criteria	Range	Sum Range
2	Product1	Half Product1	40	416
3	Product2	Product1	250
4	Product3	Some Product2	34
5		Product3	42
6		All Product3	50
Sheet1

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(SUMIF(B2:B6,"*"&A2:A4&"*",C2:C6))

 

[TomH01]

You can use wildcards (* and ?) in SUMIF(S):

Excel 2010

	A	B	C	D
1	Criteria	Range	Sum Range
2	Product1	Half Product1	40	416
3	Product2	Product1	250
4	Product3	Some Product2	34
5		Product3	42
6		All Product3	50

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =SUMPRODUCT(SUMIF(B2:B6,"*"&A2:A4&"*",C2:C6)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

That's great, thanks for that. I'd been trying the wild cards but wasn't getting the syntax quite right.