IF and AND function with COUNTIF to calculate only populated cells

V

VKiprijan

New Member
Joined
Feb 24, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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
BPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCS
3German Bundes League
4
5Ranking SystemTotalHomeAway
6RankingTeamPOINTSGDGSGAGPWDLGSGRGDPGPWDLGSGRGDPGPWDLGSGRGDP
79Eintracht Frankfurt90003411111051492441775330171326174672132-1118
81Bayern Munich100034199483344966171050461432351794437201731
97Wolfsburg7000341211954431147175733122922177462321225
1012Werder Bremen1200034107164960-11371751102527-216175662433-921
115Union Berlin500034169744321257179602591633177371923-424
1218Hertha Berlin180003469183762-2527175562326-3201714121436-227
1317Bochum161003485193369-3629176272230-8201723121139-289
148Mainz7100341211105146547176643021924176562125-423
1510B. Monchengladbach10000341011124648-2411793431181330171881530-1511
1614Hoffenheim140003497174252-1034176282427-320173591825-714
1713Augsburg130003499154055-1536176461621-522173592434-1014
184Freiburg3100341610746388581794326131331177642025-527
192Dortmund20003420577340336517131148133540177462527-225
206Bayer Leverkusen60003414811544311501782633211226176652122-124
2116Stuttgart1600034611153954-1529175462120119171791834-1610
223RB Leipzig30003417785537185817112232151735176562322123
2311FC Koln1100034912114249-739175552216620174762033-1319
2415Schalke1500034711153159-2832175472134-1319172781025-1513
25
26
27Explanation
28
29GPGames played
30WWins
31DDraws
32LLosses
33GSGoals scored
34GRGoals received
35GDGoal difference
36PPoints
37GAGoals away
Bunde League
Cell Formulas
RangeFormula
BR7:BR24BR7=RANK(CC7,$CC$7:$CC$24)
BS7:BS24BS7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($CB$7:$CB$24>CB7))
BT7:BT24BT7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BZ$7:$BZ$24>BZ7))
BU7:BU24BU7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BT$7:$BT$24=$BT7)*($CP$7:$CP$24>CP7))
BV7:CC24BV7=CD7+CL7
CD7:CD24CD7=COUNTIF($C$5:$C$346,BQ7)
CE7:CE24CE7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CF7:CF24CF7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($F$5:$F$346<>""))
CG7:CG24CG7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CH7:CH24CH7=SUMIF($C$5:$C$346,BQ7,$F$5:$F$346)
CI7:CI24CI7=SUMIF($C$5:$C$346,BQ7,$G$5:$G$346)
CJ7:CJ24,CR7:CR24CJ7=CH7-CI7
CK7:CK24,CS7:CS24CK7=CE7*3+CF7*1
CL7:CL24CL7=COUNTIF($H$5:$H$346,BQ7)
CM7:CM24CM7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CN7:CN24CN7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346=$G$5:$G$346))
CO7:CO24CO7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CP7:CP24CP7=SUMIF($H$5:$H$346,BQ7,$G$5:$G$346)
CQ7:CQ24CQ7=SUMIF($H$5:$H$346,BQ7,$F$5:$F$346)
BP7:BP24BP7=SUM(BR7:BU7)




Thank you in advance.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
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
 
Upvote 0
Solution
VKiprijan said:
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
BPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCS
3German Bundes League
4
5Ranking SystemTotalHomeAway
6RankingTeamPOINTSGDGSGAGPWDLGSGRGDPGPWDLGSGRGDPGPWDLGSGRGDP
79Eintracht Frankfurt90003411111051492441775330171326174672132-1118
81Bayern Munich100034199483344966171050461432351794437201731
97Wolfsburg7000341211954431147175733122922177462321225
1012Werder Bremen1200034107164960-11371751102527-216175662433-921
115Union Berlin500034169744321257179602591633177371923-424
1218Hertha Berlin180003469183762-2527175562326-3201714121436-227
1317Bochum161003485193369-3629176272230-8201723121139-289
148Mainz7100341211105146547176643021924176562125-423
1510B. Monchengladbach10000341011124648-2411793431181330171881530-1511
1614Hoffenheim140003497174252-1034176282427-320173591825-714
1713Augsburg130003499154055-1536176461621-522173592434-1014
184Freiburg3100341610746388581794326131331177642025-527
192Dortmund20003420577340336517131148133540177462527-225
206Bayer Leverkusen60003414811544311501782633211226176652122-124
2116Stuttgart1600034611153954-1529175462120119171791834-1610
223RB Leipzig30003417785537185817112232151735176562322123
2311FC Koln1100034912114249-739175552216620174762033-1319
2415Schalke1500034711153159-2832175472134-1319172781025-1513
25
26
27Explanation
28
29GPGames played
30WWins
31DDraws
32LLosses
33GSGoals scored
34GRGoals received
35GDGoal difference
36PPoints
37GAGoals away
Bunde League
Cell Formulas
RangeFormula
BR7:BR24BR7=RANK(CC7,$CC$7:$CC$24)
BS7:BS24BS7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($CB$7:$CB$24>CB7))
BT7:BT24BT7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BZ$7:$BZ$24>BZ7))
BU7:BU24BU7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BT$7:$BT$24=$BT7)*($CP$7:$CP$24>CP7))
BV7:CC24BV7=CD7+CL7
CD7:CD24CD7=COUNTIF($C$5:$C$346,BQ7)
CE7:CE24CE7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CF7:CF24CF7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($F$5:$F$346<>""))
CG7:CG24CG7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CH7:CH24CH7=SUMIF($C$5:$C$346,BQ7,$F$5:$F$346)
CI7:CI24CI7=SUMIF($C$5:$C$346,BQ7,$G$5:$G$346)
CJ7:CJ24,CR7:CR24CJ7=CH7-CI7
CK7:CK24,CS7:CS24CK7=CE7*3+CF7*1
CL7:CL24CL7=COUNTIF($H$5:$H$346,BQ7)
CM7:CM24CM7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CN7:CN24CN7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346=$G$5:$G$346))
CO7:CO24CO7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CP7:CP24CP7=SUMIF($H$5:$H$346,BQ7,$G$5:$G$346)
CQ7:CQ24CQ7=SUMIF($H$5:$H$346,BQ7,$F$5:$F$346)
BP7:BP24BP7=SUM(BR7:BU7)




Thank you in advance.
rondeondo said:
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
Click to expand...
Yes, that's what I did and it finally works. Thanks a lot.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
How to show last 5 games team's form
3 4 5
Replies
42
Views
2K
Peter_SSs
Peter_SSs
J
  • Solved
conditional format to change color
2
Replies
10
Views
221
SanjayGMusafir
SanjayGMusafir
S
  • Question
Help with Sumifs and countifs function
Replies
2
Views
40
Dave Patton
D
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
600
felixstraube
F
N
  • Question
rank function tie breaker
Replies
3
Views
218
Fluff
Fluff

Forum statistics

Threads
1,215,090
Messages
6,123,061
Members
449,091
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top