Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-11-2023, 02:08 PM
VKiprijan VKiprijan is offline IF and AND function with COUNTIF to calculate only populated cells Windows 10 IF and AND function with COUNTIF to calculate only populated cells Office 2016
Novice
IF and AND function with COUNTIF to calculate only populated cells
 
Join Date: May 2023
Posts: 4
VKiprijan is on a distinguished road
Default 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.
Attached Files
File Type: xlsx Example.xlsx (130.0 KB, 2 views)
Reply With Quote
  #2  
Old 05-11-2023, 10:48 PM
ArviLaanemets ArviLaanemets is offline IF and AND function with COUNTIF to calculate only populated cells Windows 8 IF and AND function with COUNTIF to calculate only populated cells Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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")
Reply With Quote
  #3  
Old 05-12-2023, 03:49 PM
VKiprijan VKiprijan is offline IF and AND function with COUNTIF to calculate only populated cells Windows 10 IF and AND function with COUNTIF to calculate only populated cells Office 2016
Novice
IF and AND function with COUNTIF to calculate only populated cells
 
Join Date: May 2023
Posts: 4
VKiprijan is on a distinguished road
Default

It works this way as well. Thanks a lot.
Reply With Quote
Reply

Thread Tools
Display Modes


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
IF and AND function with COUNTIF to calculate only populated cells Countif function not calculating correctly MoranGail Excel 1 07-09-2014 05:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft