IF and AND function with COUNTIF to calculate only populated cells
Hello Everyone,
In the attached file 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?
Thank you in advance.
|