Edit Or Combining Formula's

[Novman]

Table Help.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2				Working							Working						No Formula
3	CAR	LIGHT BLUE	238	5				CAR	LIGHT blue	238	5			CAR	LIGHT blue		5	5
4	CAR	grey		8	8			CAR	grey		8			CAR	grey		8
5	CAR	white	201	3				CAR	white	201	3	3		CAR	BLUE	201	3
6	car	black	632	4				car	black	632	4	4		car	black	632	4
7	car	grey		1	1			car	grey		1			car	DARK BLUE		1	1
8					9							7						6
9	=IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ")							=IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"")
10
11	Above is a short table with formula in green cell							Above is a short table were I would like to put a formula						Above is a short table were I would like to put a formula
12	3 checks and 1 return							Column M is what I would like the formula to return						Column M is what I would like the formula to return
13	Check 1. does B3:B7 = car							if I3:I7 = "car"						if O3:O7 = "car"
14	Check 2. does C3:C7 not have word blue							Does J3:J7 not contain word "blue"						does P3:P7 have word blue
15	Check 3. does D3:D7 not have any text							Does K3:K7 have any text						Does Q3:Q7 not have any text
16	Retune. If all criteria is met return E3:E7 in F3:F7							If all criteria is met return L3:L7 in M3:M7						If all criteria is met return R3:R7 in S3:S7
17
18					The differences between the 2 formulas would be									I have tried combining the formula's from other table's
19					theses parts of the formula									to find the formula for the above table but I cant get it right
Sheet1

Cell Formulas
Range	Formula
F3:F7	F3	=IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ")
F8,S8,M8	F8	=SUM(F3:F7)
M3:M7	M3	=IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"")

 

[ExceLoki]

try this

=IF(AND(O3="car",NOT(ISERROR(SEARCH("blue",P3))))*(Q3=""),R3,"")

-----------------

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2				Working							Working						No Formula
3	CAR	LIGHT BLUE	238	5				CAR	LIGHT blue	238	5			CAR	LIGHT blue		5	5
4	CAR	grey		8	8			CAR	grey		8			CAR	grey		8
5	CAR	white	201	3				CAR	white	201	3	3		CAR	BLUE	201	3
6	car	black	632	4				car	black	632	4	4		car	black	632	4
7	car	grey		1	1			car	grey		1			car	DARK BLUE		1	1
8					9							7						6
9
10
11	Above is a short table with formula in green cell							Above is a short table were I would like to put a formula						Above is a short table were I would like to put a formula
12	3 checks and 1 return							Column M is what I would like the formula to return						Column M is what I would like the formula to return
13	Check 1. does B3:B7 = car							if I3:I7 = "car"						if O3:O7 = "car"
14	Check 2. does C3:C7 not have word blue							Does J3:J7 not contain word "blue"						does P3:P7 have word blue
15	Check 3. does D3:D7 not have any text							Does K3:K7 have any text						Does Q3:Q7 not have any text
16	Retune. If all criteria is met return E3:E7 in F3:F7							If all criteria is met return L3:L7 in M3:M7						If all criteria is met return R3:R7 in S3:S7
17
18					The differences between the 2 formulas would be									I have tried combining the formula's from other table's
19					theses parts of the formula									to find the formula for the above table but I cant get it right
Sheet1

Cell Formulas
Range	Formula
F3:F7	F3	=IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ")
F8,S8,M8	F8	=SUM(F3:F7)
M3:M7	M3	=IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"")
S3:S7	S3	=IF(AND(O3="car",NOT(ISERROR(SEARCH("blue",P3))))*(Q3=""),R3,"")
B9	B9	=IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ")
I9	I9	=IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"")