Using 3 cell choices resulting in 1 overall choice

[gtd526]

Hello,
Looking for a correct value involving 4 different cell values.
Here is the worksheet.
A423 wants the correct answer, based on G424:G427 cell results.
If any cell in range (G424:G427) = a "L", then A423 is "L".
If any cell in range (G424:G427) = is "" or "W", then "pending".
If ALL cells in range (G424:G427) = "W", then "W".
Currently if any cell in range (G424:G427) is blank or not filled, its "pending".
Solution = only include in the range populated cells from H424:H427. 4 cells will always be the range, whether there blank of not.
I just want it to include cells being used, not necessarily the entire range.
The current formula works when the entire range is filled.
Thank you.

Wager Practice Macro.xlsm
	A	B	C	D	E	F	G	H	I	J	K
423	Pending			NFL	Parlay	$ 5.00	$ 6.56
424					Parlay		W	DEN	JAX	*
425					Parlay		W	LAR	CIN	*
426					Parlay		0
427					Parlay		0
428											$ 13.65
NFL

Cell Formulas
Range	Formula
A423	A423	=IF(COUNTIF($G424:$G427,"L")>0,"L",IF(COUNTIF($G424:$G427,0)>0,"Pending","W"))
K423	K423	=IFERROR(IF($A423=0,$F423*-1,IF($A423="W",$G423,IF($A423="L",$F423*-1,""))),"")
G424:G427	G424	=IFNA(INDEX('[NFL.xlsm]Weekly Picks'!$D$4:$D$35,MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),0)
H424:H427	H424	=IFERROR(LOOKUP(2, 1/((COUNTIF($H$423:$H423,'[NFL.xlsm]Weekly Picks'!$H$4:$H$35)=0)*('[NFL.xlsm]Weekly Picks'!$H$4:$H$35<>"")*('[NFL.xlsm]Weekly Picks'!$F$4:$F$35="*")),'[NFL.xlsm]Weekly Picks'!$H$4:$H$35),"")
I424:I427	I424	=IFERROR(IF(1=MOD(MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1)),"")
J424:J427	J424	=IFERROR(INDEX('[NFL.xlsm]Weekly Picks'!$F$4:$F$35,MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),"")
K428	K428	=SUM(K418:K427)

 

[gtd526]

Perfect. Thank you.

Hello,
Using your formula, is there a way to show blank in A174 if nothing is listed in $H175:H$178? Currently its showing "W".

Wagers.xlsm
	A	B	C	D	E	F	G	H	I	J
174	W			NFL	Parlay	$ 5.00	$ 6.56
175					Parlay		0
176					Parlay		0
177					Parlay		0
178					Parlay		0
NFL

Cell Formulas
Range	Formula
A174	A174	=IF(COUNTIF($G175:$G178,"L"),"L",IF(COUNTIF($G175:$G178,"W")=COUNTIF($H175:$H178,"?*"),"W","Pending"))
H174	H174	=IFERROR(LOOKUP(2, 1/((COUNTIF($H$169:$H173, '[NFL.xlsm]Weekly Picks'!$H$4:$H$35)=0)*( '[NFL.xlsm]Weekly Picks'!$H$4:$H$35<>"")*('[NFL.xlsm]Weekly Picks'!$F$4:$F$35<>"p")), '[NFL.xlsm]Weekly Picks'!$H$4:$H$35),"")
G175	G175	=IFERROR(IFNA(INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),0),"")
H175:H178	H175	=IFERROR(LOOKUP(2, 1/((COUNTIF($H$174:$H174,'[NFL.xlsm]Weekly Picks'!$H$4:$H$35)=0)*('[NFL.xlsm]Weekly Picks'!$H$4:$H$35<>"")*('[NFL.xlsm]Weekly Picks'!$F$4:$F$35="p")),'[NFL.xlsm]Weekly Picks'!$H$4:$H$35),"")
I175:I178	I175	=IFERROR(IF(1=MOD(MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1)),"")
J175:J178	J175	=IFERROR(INDEX('[NFL.xlsm]Weekly Picks'!$I$4:$I$35,MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),"")
G176:G178	G176	=IFNA(INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H176,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),0)

This is good.

Wagers.xlsm
	A	B	C	D	E	F	G	H	I	J
174	Pending			NFL	Parlay	$ 5.00	$ 6.56
175					Parlay		0	NE	MIA	-
176					Parlay		0	PHI	DET	-
177					Parlay		0
178					Parlay		0
NFL

Cell Formulas
Range	Formula
A174	A174	=IF(COUNTIF($G175:$G178,"L"),"L",IF(COUNTIF($G175:$G178,"W")=COUNTIF($H175:$H178,"?*"),"W","Pending"))
H174	H174	=IFERROR(LOOKUP(2, 1/((COUNTIF($H$169:$H173, '[NFL.xlsm]Weekly Picks'!$H$4:$H$35)=0)*( '[NFL.xlsm]Weekly Picks'!$H$4:$H$35<>"")*('[NFL.xlsm]Weekly Picks'!$F$4:$F$35<>"p")), '[NFL.xlsm]Weekly Picks'!$H$4:$H$35),"")
G175	G175	=IFERROR(IFNA(INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),0),"")
H175:H178	H175	=IFERROR(LOOKUP(2, 1/((COUNTIF($H$174:$H174,'[NFL.xlsm]Weekly Picks'!$H$4:$H$35)=0)*('[NFL.xlsm]Weekly Picks'!$H$4:$H$35<>"")*('[NFL.xlsm]Weekly Picks'!$F$4:$F$35="p")),'[NFL.xlsm]Weekly Picks'!$H$4:$H$35),"")
I175:I178	I175	=IFERROR(IF(1=MOD(MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1)),"")
J175:J178	J175	=IFERROR(INDEX('[NFL.xlsm]Weekly Picks'!$I$4:$I$35,MATCH($H175,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),"")
G176:G178	G176	=IFNA(INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H176,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),0)

 

[Fluff]

How about

=IF(COUNTIF($H175:$H178,"?*")=0,"",IF(COUNTIF($G175:$G178,"L"),"L",IF(COUNTIF($G175:$G178,"W")=COUNTIF($H175:$H178,"?*"),"W","Pending")))

 

[gtd526]

How about

=IF(COUNTIF($H175:$H178,"?*")=0,"",IF(COUNTIF($G175:$G178,"L"),"L",IF(COUNTIF($G175:$G178,"W")=COUNTIF($H175:$H178,"?*"),"W","Pending")))

Perfect. Thank you.

 

[Fluff]

My pleasure.