Sum range with subtracting two columns

[italy16]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov 18[/TD]
[TD]Dec 18[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]New[/TD]
[TD]R[/TD]
[TD]Customer[/TD]
[TD]45[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Old[/TD]
[TD]R[/TD]
[TD]Customer[/TD]
[TD]45[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ok[/TD]
[TD]NF[/TD]
[TD]Customer[/TD]
[TD]24[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ok[/TD]
[TD]NF[/TD]
[TD]Customer[/TD]
[TD]12[/TD]
[TD]98[/TD]
[/TR]
</tbody>[/TABLE]

Hello - I'm trying to "Sum" the different b/w Col.'s E and D IF column B is equal to either "new" "old" "Ok" AND Col C is equal to "R" or "NF"

So for row 4, it would be if B is equal to OK and Col C is equal to NF, then take the difference b/w Col E and D (which is 86)

Thank you in advance

 

[jtakw]

Hi,

Assuming you meant Row 5, not Row 4, having the result of 86, and the extra Column D "Customer" is meaningless for this query:

Excel 2010
	A	B	C	D	E	F	G
1	Row 1			18-Nov	18-Dec
2	2	New	R	45	57		12
3	3	Old	R	45	89		44
4	4	Ok	NF	24	89		65
5	5	Ok	NF	12	98		86
Sheet6

Cell Formulas
Range	Formula
G2	=IF(AND(OR(B2={"New","Old","Ok"}),OR(C2={"R","NF"})),E2-D2,"")

G2 formula copied down.

 

[markmzz]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov 18[/TD]
[TD]Dec 18[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]New[/TD]
[TD]R[/TD]
[TD]Customer[/TD]
[TD]45[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Old[/TD]
[TD]R[/TD]
[TD]Customer[/TD]
[TD]45[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ok[/TD]
[TD]NF[/TD]
[TD]Customer[/TD]
[TD]24[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ok[/TD]
[TD]NF[/TD]
[TD]Customer[/TD]
[TD]12[/TD]
[TD]98[/TD]
[/TR]
</tbody>[/TABLE]

Hello - I'm trying to "Sum" the different b/w Col.'s E and D IF column B is equal to either "new" "old" "Ok" AND Col C is equal to "R" or "NF"

So for row 4, it would be if B is equal to OK and Col C is equal to NF, then take the difference b/w Col E and D (which is 86)

Thank you in advance

Maybe this:

=SUMPRODUCT(F2:F5-E2:E5,--(MMULT(--ISNUMBER(SEARCH(" "&B2:C5&" "," New Old Ok R NF ")),{1;1})=2))

Markmzz