IF and AND function with COUNTIF to calculate only populated cells (continued)

[VKiprijan]

In addition to my previous post, here is the table with the soccer games from which the ranking table draws data:

Example.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
3	Round									Result			Total Goals
4	Date	Home	1st Half		2nd Half		Away	POINTS		45'	90'	KT	1 PP	2 PP	0 - 1	0 - 2	2 - 3	2 - 4	3 - 5	4 - 6	2+	3+	4+	5+	7+	GG	GG3+	GG4+	1 GG	2 GG	G 1PP	G 2PP	VG	X 1PP	OG D/G	DP	2+ 1PP	2+ 2PP
5	05.08.2022	Eintracht Frankfurt	0	5	1	6	Bayern Munich	0	3	2	2	2	5	2							2+	3+	4+	5+	7+	GG	GG3+	GG4+		2GG	3+	2+	1>2		GG3+	DP	2+ 1PP	2+ 2PP
6	06.08.2022	Wolfsburg	1	2	2	2	Werder Bremen	1	1	2	X	X	3	1				2-4	3-5	4-6	2+	3+	4+			GG	GG3+	GG4+	1GG	2GG	3+	1+	1>2		GG3+		2+ 1PP	2+ 2PP
7	06.08.2022	Union Berlin	1	0	3	1	Hertha Berlin	3	0	1	1	1	1	3				2-4	3-5	4-6	2+	3+	4+			GG	GG3+	GG4+		2GG	1+	3+	2>1		GG3+	DP		2+ 2PP
8	06.08.2022	Bochum	1	1	1	2	Mainz	0	3	X	2	2	2	1			2-3	2-4	3-5		2+	3+				GG	GG3+		1GG	2GG	2+	1+	1>2	X	GG3+		2+ 1PP	2+ 2PP
9	06.08.2022	B. Monchengladbach	1	1	3	1	Hoffenheim	3	0	X	1	1	2	2				2-4	3-5	4-6	2+	3+	4+			GG	GG3+	GG4+	1GG	2GG	2+	2+	1=2	X	GG3+		2+ 1PP	2+ 2PP
10	06.08.2022	Augsburg	0	0	0	4	Freiburg	0	3	X	2	2	0	4				2-4	3-5	4-6	2+	3+	4+								1+	3+	2>1	X	DNDG			2+ 2PP
11	06.08.2022	Dortmund	1	0	1	0	Bayer Leverkusen	3	0	1	1	1	1	0	0-1	0-2															1+	NG	1>2		GNDG
12	07.08.2022	Stuttgart	1	1	1	1	RB Leipzig	1	1	X	X	X	2	0		0-2	2-3	2-4			2+					GG			1GG	2GG	2+	NG	1>2	X	GG		2+ 1PP	2+ 2PP
13	07.08.2022	FC Koln	0	0	3	1	Schalke	3	0	X	1	1	0	4				2-4	3-5	4-6	2+	3+	4+			GG	GG3+	GG4+		2GG	1+	3+	2>1	X	GG3+			2+ 2PP
Bunde League

Cell Formulas
Range	Formula
I5:I13	I5	=IF(F5="","",IF(F5>G5,3,IF(F5=G5,1,0)))
J5:J13	J5	=IF(G5="","",IF(G5>F5,3,IF(G5=F5,1,0)))
K5:K13	K5	=IF(D5=""," ",IF(D5-E5>0,1,IF(D5-E5<0,2,"X")))
L5:L13	L5	=IF(F5=""," ",IF(F5-G5>0,1,IF(F5-G5<0,2,"X")))
M5:M13	M5	=IF(F5="","",IF(F5=G5,"X",IF(F5<G5,2,IF(F5>G5,1,0))))
N5:N13	N5	=IF(AND(D5<>"",E5<>""),D5+E5,"")
O5:O13	O5	=IF(AND(F5<>"",G5<>""),(F5+G5)-(D5+E5),"")
P5:P13	P5	=IF(AND(F5<>"",G5<>"",F5+G5<=1),"0-1","")
Q5:Q13	Q5	=IF(AND(F5<>"",G5<>"",F5+G5<=2),"0-2","")
R5:R13	R5	=IF(F5+G5=2,"2-3",IF(F5+G5=3,"2-3",""))
S5:S13	S5	=IF(F5+G5=2,"2-4",IF(F5+G5=3,"2-4",IF(F5+G5=4,"2-4","")))
T5:T13	T5	=IF(F5+G5=3,"3-5",IF(F5+G5=4,"3-5",IF(F5+G5=5,"3-5","")))
U5:U13	U5	=IF(F5+G5=4,"4-6",IF(F5+G5=5,"4-6",IF(F5+G5=6,"4-6","")))
V5:V13	V5	=IF(F5+G5="","",IF(F5+G5>=2,"2+",""))
W5:W13	W5	=IF(F5+G5="","",IF(F5+G5>=3,"3+",""))
X5:X13	X5	=IF(F5+G5="","",IF(F5+G5>=4,"4+",""))
Y5:Y13	Y5	=IF(F5+G5="","",IF(F5+G5>=5,"5+",""))
Z5:Z13	Z5	=IF(F5+G5="","",IF(F5+G5>=7,"7+",""))
AA5:AA13	AA5	=IF(AND(F5>0,G5>0),"GG","")
AB5:AB13	AB5	=IF(AND(D5+F5>0,E5+G5>0,F5+G5>2),"GG3+","")
AC5:AC13	AC5	=IF(AND(D5+F5>0,E5+G5>0,F5+G5>3),"GG4+","")
AD5:AD13	AD5	=IF(AND(D5>0,E5>0),"1GG","")
AE5:AE13	AE5	=IF(AND(F5>0,G5>0),"2GG","")
AF5:AF13	AF5	=IF(N5="","",IF(113<1,"NG",IF(N5<2,"1+",IF(N5<3,"2+","3+"))))
AG5:AG13	AG5	=IF(O5="","",IF(O5<1,"NG",IF(O5<2,"1+",IF(O5<3,"2+","3+"))))
AH5:AH13	AH5	=IF(AND(N5<>"",O5<>""),IF(N5<O5,"2>1",IF(N5>O5,"1>2","1=2")),"")
AI5:AI13	AI5	=IF(AND(D5<>"",E5<>"",D5=E5),"X","")
AJ5:AJ13	AJ5	=IF(N5="","",IF(F5=0,IF(G5=0,"NG","DNDG"),IF(F5>0,IF(G5>0,IF(F5+G5>=3,"GG3+","GG"),"GNDG"),"DNDG")))
AK5:AK13	AK5	=IF(OR(AND(D5>E5,F5>G5,F5>D5),AND(E5>D5,G5>F5,G5>E5)),"DP","")
AL5:AL13	AL5	=IF(D5+E5>=2,"2+ 1PP","")
AM5:AM13	AM5	=IF(F5+G5>=2,"2+ 2PP","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H5:H13	Cell Value	duplicates	text	NO
C5:C13	Cell Value	duplicates	text	NO

 

[Joe4]

Duplicate to: IF and AND function with COUNTIF to calculate only populated cells

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted. In relation to your question here, I have closed this thread so please continue in the linked thread.