IF and AND function with COUNTIF to calculate only populated cells

[VKiprijan]

Hello Everyone,

In the attached table for Soccer League, I am trying to come up with a formula that will count only the home and away games that have been played (CD for home and CL for away columns, the formula is =COUNTIF($C$5:$C$346,BQ7)), not all the games in the season. For example, the whole season has 34 weeks and I want the ranking based only on the 31 week that was played. The existing formula is showing the exact points and ranking only if I remove the remaining games that I have put as fixtures. If I keep these upcoming games, the table counts all the games I listed as played which is wrong. Basically, I want the formula that will work properly with all the soccer games in one season, finished ones and the upcoming ones.
I guess I should use the IF and AND in conjunction with the COUNTIF function to be able to set the scores in the first half and the second half of the games (columns D, E, F and G) as criteria for counting these games played. Can someone write me how the formula should look like?

Example.xlsx
	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS
3	German Bundes League
4
5			Ranking System				Total								Home								Away
6	Ranking	Team	POINTS	GD	GS	GA	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P
7	9	Eintracht Frankfurt	9	0	0	0	34	11	11	10	51	49	2	44	17	7	5	3	30	17	13	26	17	4	6	7	21	32	-11	18
8	1	Bayern Munich	1	0	0	0	34	19	9	4	83	34	49	66	17	10	5	0	46	14	32	35	17	9	4	4	37	20	17	31
9	7	Wolfsburg	7	0	0	0	34	12	11	9	54	43	11	47	17	5	7	3	31	22	9	22	17	7	4	6	23	21	2	25
10	12	Werder Bremen	12	0	0	0	34	10	7	16	49	60	-11	37	17	5	1	10	25	27	-2	16	17	5	6	6	24	33	-9	21
11	5	Union Berlin	5	0	0	0	34	16	9	7	44	32	12	57	17	9	6	0	25	9	16	33	17	7	3	7	19	23	-4	24
12	18	Hertha Berlin	18	0	0	0	34	6	9	18	37	62	-25	27	17	5	5	6	23	26	-3	20	17	1	4	12	14	36	-22	7
13	17	Bochum	16	1	0	0	34	8	5	19	33	69	-36	29	17	6	2	7	22	30	-8	20	17	2	3	12	11	39	-28	9
14	8	Mainz	7	1	0	0	34	12	11	10	51	46	5	47	17	6	6	4	30	21	9	24	17	6	5	6	21	25	-4	23
15	10	B. Monchengladbach	10	0	0	0	34	10	11	12	46	48	-2	41	17	9	3	4	31	18	13	30	17	1	8	8	15	30	-15	11
16	14	Hoffenheim	14	0	0	0	34	9	7	17	42	52	-10	34	17	6	2	8	24	27	-3	20	17	3	5	9	18	25	-7	14
17	13	Augsburg	13	0	0	0	34	9	9	15	40	55	-15	36	17	6	4	6	16	21	-5	22	17	3	5	9	24	34	-10	14
18	4	Freiburg	3	1	0	0	34	16	10	7	46	38	8	58	17	9	4	3	26	13	13	31	17	7	6	4	20	25	-5	27
19	2	Dortmund	2	0	0	0	34	20	5	7	73	40	33	65	17	13	1	1	48	13	35	40	17	7	4	6	25	27	-2	25
20	6	Bayer Leverkusen	6	0	0	0	34	14	8	11	54	43	11	50	17	8	2	6	33	21	12	26	17	6	6	5	21	22	-1	24
21	16	Stuttgart	16	0	0	0	34	6	11	15	39	54	-15	29	17	5	4	6	21	20	1	19	17	1	7	9	18	34	-16	10
22	3	RB Leipzig	3	0	0	0	34	17	7	8	55	37	18	58	17	11	2	2	32	15	17	35	17	6	5	6	23	22	1	23
23	11	FC Koln	11	0	0	0	34	9	12	11	42	49	-7	39	17	5	5	5	22	16	6	20	17	4	7	6	20	33	-13	19
24	15	Schalke	15	0	0	0	34	7	11	15	31	59	-28	32	17	5	4	7	21	34	-13	19	17	2	7	8	10	25	-15	13
25
26
27		Explanation
28
29	GP	Games played
30	W	Wins
31	D	Draws
32	L	Losses
33	GS	Goals scored
34	GR	Goals received
35	GD	Goal difference
36	P	Points
37	GA	Goals away
Bunde League

Cell Formulas
Range	Formula
BR7:BR24	BR7	=RANK(CC7,$CC$7:$CC$24)
BS7:BS24	BS7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($CB$7:$CB$24>CB7))
BT7:BT24	BT7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BZ$7:$BZ$24>BZ7))
BU7:BU24	BU7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BT$7:$BT$24=$BT7)*($CP$7:$CP$24>CP7))
BV7:CC24	BV7	=CD7+CL7
CD7:CD24	CD7	=COUNTIF($C$5:$C$346,BQ7)
CE7:CE24	CE7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CF7:CF24	CF7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($F$5:$F$346<>""))
CG7:CG24	CG7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CH7:CH24	CH7	=SUMIF($C$5:$C$346,BQ7,$F$5:$F$346)
CI7:CI24	CI7	=SUMIF($C$5:$C$346,BQ7,$G$5:$G$346)
CJ7:CJ24,CR7:CR24	CJ7	=CH7-CI7
CK7:CK24,CS7:CS24	CK7	=CE7*3+CF7*1
CL7:CL24	CL7	=COUNTIF($H$5:$H$346,BQ7)
CM7:CM24	CM7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CN7:CN24	CN7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346=$G$5:$G$346))
CO7:CO24	CO7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CP7:CP24	CP7	=SUMIF($H$5:$H$346,BQ7,$G$5:$G$346)
CQ7:CQ24	CQ7	=SUMIF($H$5:$H$346,BQ7,$F$5:$F$346)
BP7:BP24	BP7	=SUM(BR7:BU7)

Thank you in advance.

 

[rondeondo]

Hi VK
change your countif() to countifs() and you can include multiple criteria, then your second criteria can look at the date of the game or something suitable to identify the games to count or a date range

 

[VKiprijan]

Hello Everyone,

In the attached table for Soccer League, I am trying to come up with a formula that will count only the home and away games that have been played (CD for home and CL for away columns, the formula is =COUNTIF($C$5:$C$346,BQ7)), not all the games in the season. For example, the whole season has 34 weeks and I want the ranking based only on the 31 week that was played. The existing formula is showing the exact points and ranking only if I remove the remaining games that I have put as fixtures. If I keep these upcoming games, the table counts all the games I listed as played which is wrong. Basically, I want the formula that will work properly with all the soccer games in one season, finished ones and the upcoming ones.
I guess I should use the IF and AND in conjunction with the COUNTIF function to be able to set the scores in the first half and the second half of the games (columns D, E, F and G) as criteria for counting these games played. Can someone write me how the formula should look like?

Example.xlsx
	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS
3	German Bundes League
4
5			Ranking System				Total								Home								Away
6	Ranking	Team	POINTS	GD	GS	GA	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P
7	9	Eintracht Frankfurt	9	0	0	0	34	11	11	10	51	49	2	44	17	7	5	3	30	17	13	26	17	4	6	7	21	32	-11	18
8	1	Bayern Munich	1	0	0	0	34	19	9	4	83	34	49	66	17	10	5	0	46	14	32	35	17	9	4	4	37	20	17	31
9	7	Wolfsburg	7	0	0	0	34	12	11	9	54	43	11	47	17	5	7	3	31	22	9	22	17	7	4	6	23	21	2	25
10	12	Werder Bremen	12	0	0	0	34	10	7	16	49	60	-11	37	17	5	1	10	25	27	-2	16	17	5	6	6	24	33	-9	21
11	5	Union Berlin	5	0	0	0	34	16	9	7	44	32	12	57	17	9	6	0	25	9	16	33	17	7	3	7	19	23	-4	24
12	18	Hertha Berlin	18	0	0	0	34	6	9	18	37	62	-25	27	17	5	5	6	23	26	-3	20	17	1	4	12	14	36	-22	7
13	17	Bochum	16	1	0	0	34	8	5	19	33	69	-36	29	17	6	2	7	22	30	-8	20	17	2	3	12	11	39	-28	9
14	8	Mainz	7	1	0	0	34	12	11	10	51	46	5	47	17	6	6	4	30	21	9	24	17	6	5	6	21	25	-4	23
15	10	B. Monchengladbach	10	0	0	0	34	10	11	12	46	48	-2	41	17	9	3	4	31	18	13	30	17	1	8	8	15	30	-15	11
16	14	Hoffenheim	14	0	0	0	34	9	7	17	42	52	-10	34	17	6	2	8	24	27	-3	20	17	3	5	9	18	25	-7	14
17	13	Augsburg	13	0	0	0	34	9	9	15	40	55	-15	36	17	6	4	6	16	21	-5	22	17	3	5	9	24	34	-10	14
18	4	Freiburg	3	1	0	0	34	16	10	7	46	38	8	58	17	9	4	3	26	13	13	31	17	7	6	4	20	25	-5	27
19	2	Dortmund	2	0	0	0	34	20	5	7	73	40	33	65	17	13	1	1	48	13	35	40	17	7	4	6	25	27	-2	25
20	6	Bayer Leverkusen	6	0	0	0	34	14	8	11	54	43	11	50	17	8	2	6	33	21	12	26	17	6	6	5	21	22	-1	24
21	16	Stuttgart	16	0	0	0	34	6	11	15	39	54	-15	29	17	5	4	6	21	20	1	19	17	1	7	9	18	34	-16	10
22	3	RB Leipzig	3	0	0	0	34	17	7	8	55	37	18	58	17	11	2	2	32	15	17	35	17	6	5	6	23	22	1	23
23	11	FC Koln	11	0	0	0	34	9	12	11	42	49	-7	39	17	5	5	5	22	16	6	20	17	4	7	6	20	33	-13	19
24	15	Schalke	15	0	0	0	34	7	11	15	31	59	-28	32	17	5	4	7	21	34	-13	19	17	2	7	8	10	25	-15	13
25
26
27		Explanation
28
29	GP	Games played
30	W	Wins
31	D	Draws
32	L	Losses
33	GS	Goals scored
34	GR	Goals received
35	GD	Goal difference
36	P	Points
37	GA	Goals away
Bunde League

Cell Formulas
Range	Formula
BR7:BR24	BR7	=RANK(CC7,$CC$7:$CC$24)
BS7:BS24	BS7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($CB$7:$CB$24>CB7))
BT7:BT24	BT7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BZ$7:$BZ$24>BZ7))
BU7:BU24	BU7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BT$7:$BT$24=$BT7)*($CP$7:$CP$24>CP7))
BV7:CC24	BV7	=CD7+CL7
CD7:CD24	CD7	=COUNTIF($C$5:$C$346,BQ7)
CE7:CE24	CE7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CF7:CF24	CF7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($F$5:$F$346<>""))
CG7:CG24	CG7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CH7:CH24	CH7	=SUMIF($C$5:$C$346,BQ7,$F$5:$F$346)
CI7:CI24	CI7	=SUMIF($C$5:$C$346,BQ7,$G$5:$G$346)
CJ7:CJ24,CR7:CR24	CJ7	=CH7-CI7
CK7:CK24,CS7:CS24	CK7	=CE7*3+CF7*1
CL7:CL24	CL7	=COUNTIF($H$5:$H$346,BQ7)
CM7:CM24	CM7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CN7:CN24	CN7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346=$G$5:$G$346))
CO7:CO24	CO7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CP7:CP24	CP7	=SUMIF($H$5:$H$346,BQ7,$G$5:$G$346)
CQ7:CQ24	CQ7	=SUMIF($H$5:$H$346,BQ7,$F$5:$F$346)
BP7:BP24	BP7	=SUM(BR7:BU7)

Thank you in advance.

Hi VK
change your countif() to countifs() and you can include multiple criteria, then your second criteria can look at the date of the game or something suitable to identify the games to count or a date range

Yes, that's what I did and it finally works. Thanks a lot.