returning a value if multiple criteria are true

[jbrown021286]

hours log.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	4/26/2024
2		Day	date	notes	Column2	RO	status	SUM	Daignosis	CODE	JOB	FLAG TIME	ADJUSTMENT	Column1
3		Monday	4/22/2024			6208395	g	1.0					1			don’t count	Waiting	incorect
4		Tuesday	4/23/2024			6209405	g	0.2	window molding				0.2			0	0	0
5		Monday	4/22/2024			6209839	g	1.0					1			Total	Not Verifyed	Verifyed
6		Saturday	4/20/2024			6209943	g	1.0		k	key	1				64.1	10.2	53.9
7		Monday	4/22/2024			6209944	g	0.5					0.5
8		Monday	4/22/2024			6209944	g	0.5					0.5			Day	Hours	Average
9		Monday	4/22/2024			6209944	g	1.5					1.5		x	Monday	8.6	15.775
10		Wednesday	4/24/2024			6209974	g	0.1		af	air filter	0.1			x	Tuesday	15.3
11		Wednesday	4/24/2024			6209974	g	0.2		cf	cabin filter	0.2			x	Wednesday	29
12		Wednesday	4/24/2024			6209974	g	0.5		ts	Trans service	0.5			x	Thursday	0
13		Wednesday	4/24/2024			6209974	g	0.7		bs	Brake Service	0.8	-0.1		x	Friday	0
14		Wednesday	4/24/2024			6209974	g	1.0		cs	Coolant Service	1				Saturday	1
15		Wednesday	4/24/2024			6209974	g	1.5		br	turn rotors	1.5
16		Wednesday	4/24/2024			6209974	g	3.0					3		x	Today	10.2
17		Monday	4/22/2024			6209996	g	0.7					0.7
18		Monday	4/22/2024			6209996	g	1.4					1.4
19		Tuesday	4/23/2024			6209999	g	0.7		pro	propack	0.7
20		Tuesday	4/23/2024			6209999	g	1.4		pdi	pdi	1.4
21		Monday	4/22/2024			6210011	g	2.0		9ts	9speed	2
22		Tuesday	4/23/2024			6210012	g	1.0	head light rest				1
23		Tuesday	4/23/2024			6210012	g	2.0	ps pump				2
24		Tuesday	4/23/2024			6210012	g	10.0	oil pump				10
25		Wednesday	4/24/2024			6210042	g	0.1					0.1
26		Wednesday	4/24/2024			6210042	g	0.2		af			0.2
27		Wednesday	4/24/2024			6210042	g	0.3		cf			0.3			X	Incorrect
28		Wednesday	4/24/2024			6210042	g	4.0					4			G	Verifyed
29		Wednesday	4/24/2024			6210042	g	6.0					6			W	Waiting
30		Wednesday	4/24/2024			6210094	g	1.5					1.5			N	Don’t Count
31		Wednesday	4/24/2024			6210094	g	3.0					3			c	Need to check
32		Wednesday	4/24/2024			6210178	g	1.4		pdi	pdi	1.4				nf	closed not flaged
33		Wednesday	4/24/2024			6210185	g	2.0	adas				2			wtf	Wrong tech flaged
34		Wednesday	4/24/2024			6210185	g	2.0					2
35		Wednesday	4/24/2024			6210192	g	1.5					1.5
36		Friday	4/26/2024			6209847		1.0		d	Diag	1
37		Friday	4/26/2024			6210182		2.0					2
38		Friday	4/26/2024			62209975		3.0					3
39		Friday	4/26/2024			6210326		1.4		PDI	pdi	1.4
40		Friday	4/26/2024			6210326		0.7		PRO	propack	0.7
41		Friday	4/26/2024			6210324		1.4		PDI	pdi	1.4
Log

Cell Formulas
Range	Formula
A1	A1	=TODAY()
C4,C35:C41,C30:C33,C19:C24,C10:C16,C6	C4	=IF([@RO]<>"",IF(C4="",TODAY(),C4),"")
P4	P4	=SUMIF(G:G,"N",H:H)+SUMIF(G:G,"cnf",H:H)
Q4	Q4	=SUMIF(G:G,"w",H:H)
R4	R4	=SUMIF(G:G,"x",H:H)
P6	P6	=SUM(H:H,)-SUMIF(G:G,"N",H:H)-SUMIF(G:G,"nf",H:H)-Q2
Q6	Q6	=SUMIF(G:G,"",H:H)+SUMIF(G:G,"c",H:H)
R6	R6	=SUMIF(G:G,"G",H:H)-Q2
R9	R9	=AVERAGEIFS(Q9:Q16,Q9:Q16,">0",O9:O16,"X")
Q9:Q14	Q9	=SUMIFS($H$3:$H$280,$B$3:$B$280,P9,$C$3:$C$280,"<"&A$1)
Q16	Q16	=SUMIF($C$3:$C$280,A1,$H3:$H280)
K3:K25,K28:K41	K3	=IF(J3="","",INDEX($AA$3:$AA$91,MATCH(J3,$Z$3:$Z$91,0),1))
L3:L41	L3	=IF(K3="","",INDEX($AB$3:$AB$91,MATCH(J3,$Z$3:$Z$91,0),1))
B3:B41	B3	=IFERROR(TEXT(WEEKDAY($C3),"Dddd"),"")
H3:H41	H3	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:E280	Expression	=(E3="")*(F3<>"")	text	NO
E3:E280	Expression	=(E3="x")*(F3<>"")	text	NO
Q30,Q77	Expression	=COUNTIF(AG:AG,Q30)>0	text	NO
M22:M52,F3:F280	Expression	=COUNTIF(W:W,F3)>0	text	NO
G3:G280	Cell Value	=$P$33	text	NO
I5:I7	Cell Value	=$P$32	text	NO
I5:I7	Cell Value	=$P$31	text	NO
I5:I7	Cell Value	=$P$30	text	NO
I5:I7	Cell Value	=$P$29	text	NO
I5:I7	Cell Value	=$P$28	text	NO
I5:I7	Cell Value	=$P$27	text	NO
I19	Cell Value	=$P$32	text	NO
I19	Cell Value	=$P$31	text	NO
I19	Cell Value	=$P$30	text	NO
I19	Cell Value	=$P$29	text	NO
I19	Cell Value	=$P$28	text	NO
I19	Cell Value	=$P$27	text	NO
G3:G280	Cell Value	=$P$32	text	NO
G3:H280	Cell Value	=$P$31	text	NO
G3:H280	Cell Value	=$P$30	text	NO
G3:H280	Cell Value	=$P$29	text	NO
G3:H280	Cell Value	=$P$28	text	NO
G3:H280	Cell Value	=$P$27	text	NO
H3:H280	Expression	=" =COUNTIFS(D:D,A1,E:E,J2)>0"	text	NO
H3:H280	Expression	=COUNTIFS(W:W,F3,X:X,H3)>0	text	NO
M3:O5,N6:O7	Expression	=COUNTIFS(XFA:XFA,L3,XFC:XFC,M3)>0	text	NO
F28:F38	Expression	=COUNTIF(XEU:XEU,F28)>0	text	NO

i am wanting to have a formula in column E that if column F has a value in it and column G is blank then the cell in E returns "X" and if f has a value and G is G has "G" then E is blank and if both F and G are blank then Eis blank. any help would be appreciated

 

[etaf]

formula in column E
if column F has a value in it and column G is blank then the cell in E returns "X"
if f has a value and G has "G" then E is blank
if both F and G are blank then Eis blank.

how about
in E3 - copied down
=IF( OR(AND( F3="",G3=""),AND(F3<>"",G3="G")), "", IF( AND(F3<>"", G3=""), "X", "Criteria NOT met"))


added to xl2bb

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	4/26/24
2		Day	date	notes	Column2	RO	status	SUM	Daignosis	CODE	JOB	FLAG TIME	ADJUSTMENT	Column1
3		Monday	45404			6208395	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					1			don’t count	Waiting	incorect
4			=IF([@RO]<>"",IF(C4="",TODAY(),C4),"")			6209405	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])	window molding				0.2			0	0	0
5		Monday	45404			6209839	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					1			Total	Not Verifyed	Verifyed
6			=IF([@RO]<>"",IF(C6="",TODAY(),C6),"")			6209943	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		k	#N/A	#N/A				0	0	0
7		Monday	45404			6209944	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					0.5
8		Monday	45404			6209944	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					0.5			Day	Hours	Average
9		Monday	45404			6209944	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					1.5		x	Monday	0	#DIV/0!
10			=IF([@RO]<>"",IF(C10="",TODAY(),C10),"")			6209974	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		af	#N/A	#N/A			x	Tuesday	0
11			=IF([@RO]<>"",IF(C11="",TODAY(),C11),"")			6209974	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		cf	#N/A	#N/A			x	Wednesday	0
12			=IF([@RO]<>"",IF(C12="",TODAY(),C12),"")			6209974	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		ts	#N/A	#N/A			x	Thursday	0
13			=IF([@RO]<>"",IF(C13="",TODAY(),C13),"")			6209974	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		bs	#N/A	#N/A	-0.1		x	Friday	0
14			=IF([@RO]<>"",IF(C14="",TODAY(),C14),"")			6209974	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		cs	#N/A	#N/A				Saturday	0
15			=IF([@RO]<>"",IF(C15="",TODAY(),C15),"")			6209974	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		br	#N/A	#N/A
16			=IF([@RO]<>"",IF(C16="",TODAY(),C16),"")			6209974	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					3		x	Today	0
17		Monday	45404			6209996	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					0.7
18		Monday	45404			6209996	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					1.4
19			=IF([@RO]<>"",IF(C19="",TODAY(),C19),"")			6209999	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		pro	#N/A	#N/A
20			=IF([@RO]<>"",IF(C20="",TODAY(),C20),"")			6209999	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		pdi	#N/A	#N/A
21			=IF([@RO]<>"",IF(C21="",TODAY(),C21),"")			6210011	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		9ts	#N/A	#N/A
22			=IF([@RO]<>"",IF(C22="",TODAY(),C22),"")			6210012	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])	head light rest				1
23			=IF([@RO]<>"",IF(C23="",TODAY(),C23),"")			6210012	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])	ps pump				2
24			=IF([@RO]<>"",IF(C24="",TODAY(),C24),"")			6210012	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])	oil pump				10
25		Wednesday	45406			6210042	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					0.1
26		Wednesday	45406			6210042	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		af			0.2
27		Wednesday	45406			6210042	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		cf			0.3			X	Incorrect
28		Wednesday	45406			6210042	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					4			G	Verifyed
29		Wednesday	45406			6210042	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					6			W	Waiting
30			=IF([@RO]<>"",IF(C30="",TODAY(),C30),"")			6210094	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					1.5			N	Don’t Count
31			=IF([@RO]<>"",IF(C31="",TODAY(),C31),"")			6210094	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					3			c	Need to check
32			=IF([@RO]<>"",IF(C32="",TODAY(),C32),"")			6210178	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		pdi	#N/A	#N/A				nf	closed not flaged
33			=IF([@RO]<>"",IF(C33="",TODAY(),C33),"")			6210185	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])	adas				2			wtf	Wrong tech flaged
34		Wednesday	45406			6210185	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					2
35			=IF([@RO]<>"",IF(C35="",TODAY(),C35),"")			6210192	g	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					1.5
36			=IF([@RO]<>"",IF(C36="",TODAY(),C36),"")		X	6209847		=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		d	#N/A	#N/A
37			=IF([@RO]<>"",IF(C37="",TODAY(),C37),"")		X	6210182		=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					2
38			=IF([@RO]<>"",IF(C38="",TODAY(),C38),"")		X	62209975		=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])					3
39			=IF([@RO]<>"",IF(C39="",TODAY(),C39),"")		X	6210326		=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		PDI	#N/A	#N/A
40			=IF([@RO]<>"",IF(C40="",TODAY(),C40),"")		X	6210326		=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		PRO	#N/A	#N/A
41			=IF([@RO]<>"",IF(C41="",TODAY(),C41),"")		X	6210324		=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])		PDI	#N/A	#N/A
Sheet2

Cell Formulas
Range	Formula
A1	A1	=TODAY()
P4	P4	=SUMIF(G:G,"N",H:H)+SUMIF(G:G,"cnf",H:H)
Q4	Q4	=SUMIF(G:G,"w",H:H)
R4	R4	=SUMIF(G:G,"x",H:H)
P6	P6	=SUM(H:H,)-SUMIF(G:G,"N",H:H)-SUMIF(G:G,"nf",H:H)-Q2
Q6	Q6	=SUMIF(G:G,"",H:H)+SUMIF(G:G,"c",H:H)
R6	R6	=SUMIF(G:G,"G",H:H)-Q2
R9	R9	=AVERAGEIFS(Q9:Q16,Q9:Q16,">0",O9:O16,"X")
Q9:Q14	Q9	=SUMIFS($H$3:$H$280,$B$3:$B$280,P9,$C$3:$C$280,"<"&A$1)
Q16	Q16	=SUMIF($C$3:$C$280,A1,$H3:$H280)
K3:K25,K28:K41	K3	=IF(J3="","",INDEX($AA$3:$AA$91,MATCH(J3,$Z$3:$Z$91,0),1))
L3:L41	L3	=IF(K3="","",INDEX($AB$3:$AB$91,MATCH(J3,$Z$3:$Z$91,0),1))
B3:B41	B3	=IFERROR(TEXT(WEEKDAY($C3),"Dddd"),"")
E3:E41	E3	=IF( OR(AND( F3="",G3=""),AND(F3<>"",G3="G")), "", IF( AND(F3<>"", G3=""), "X", "Criteria NOT met"))

 

[jbrown021286]

that got it thanks

 

[etaf]

you are welcome