#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Instead COUNTIF use COUNTIFS - it allows many conditions to be applied. E.g.:
Code:
To count home games =COUNTIFS($C$5:$C$346,$BQ7,$B$5:$B$346,">0") To count away games =COUNTIFS($H$5:$H$346,$BQ7,$B$5:$B$346,">0") |
#3
|
|||
|
|||
It works this way as well. Thanks a lot.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
COUNTIF to count long text/values in cells (#VALUE! Error) | FrancisSIP | Excel | 5 | 10-29-2020 04:00 AM |
Naming Documents From Populated Cells | TYPOGRAPHICS | Word VBA | 8 | 04-08-2016 09:13 AM |
Nested Countif Function | bdavidson22 | Excel | 1 | 12-21-2014 07:55 AM |
Nested Countif Function | bdavidson22 | Excel | 0 | 12-19-2014 12:36 PM |
Countif function not calculating correctly | MoranGail | Excel | 1 | 07-09-2014 05:50 AM |