Sumifs where string contains two separate cell references sequentially

[DobsonW]

Hi,

can anyone help me with the below:

• I have delimiter separated strings in column A
• A value in column B
• Two separate strings in two reference cells (C1 and D1)
• I need to sum the values in column b where the string in column A contains first C1 then D1

I've so far tried =SUMIFS($A:$A,$B:$B,"*"&C1&"*"*"*"&D1&"*"

but this doesn't work.

If i was just counting if they both appeared in the string then no problem but D1 must come after C1

In the below i would expect the result to be 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]String[/TD]
[TD]Value[/TD]
[TD]one[/TD]
[TD]four[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]one >> two >> three >> four[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]one >> two >> four >> three[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]four >> two >> one >> three[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Book1
	A	B	C	D	E
1	String	Value	one	four	2
2	one >> two >> three >> four	1
3	one >> two >> four >> three	1
4	four >> two >> one >> three	1
Sheet1

Cell Formulas
Range	Formula
E1	=SUMIF($A:$A,"*"&$C$1&"*"&$D$1&"*",$B:$B)

Is this right?

WBD

 

[DobsonW]

That is frustratingly simple and yes. works great.

Thanks very much!

 

[Aladin Akyurek]

A SUMIFS formula should work too...

In C2 enter, copy across, and down:

=SUMIFS($B$2:$B$4,$A$2:$A$4,"*"&C$1&"*")