Sheet Formula
Happy 2024 everyone, I hope your year is off to a great start! I'm back requesting help to get some formulas working. I am trying to use the countif formula to count a column in a sheet. I am trying to get it to count all the dates in the column, but I also want to use to the formula to count all the rows with that same column where there is no date. In other word I want to count all of my projects that have a completion date, and then count all my projects that do not. Lastly I want to use the countif formula to count all my projects year to date. Can someone assist with this?
Best Answer

Hi @Nick Wilson ,
I believe the three formulas you need are as follows:
 All the rows where there is a date  =COUNTIF([DateColumn]:[DateColumn],@cell<>"")
 All the rows where there is not a date  =COUNTIF([DateColumn]:[DateColumn],@cell="")
 All the rows where the date is this year  =COUNTIFS([Date Column]:[Date Column], @cell <> "", [Date Column]:[Date Column], YEAR(@cell) = YEAR(TODAY()))
It is worth noting that the second formula as is will return all blank rows at the end of a sheet so using the formula below referencing a cell which should always have a value as per the formula below.
=COUNTIFS([DateColumn]:[DateColumn], @cell = "", [OtherColumn]:[OtherColumn], @cell <> "")
Hope this helps!
John
Answers

Hi @Nick Wilson ,
I believe the three formulas you need are as follows:
 All the rows where there is a date  =COUNTIF([DateColumn]:[DateColumn],@cell<>"")
 All the rows where there is not a date  =COUNTIF([DateColumn]:[DateColumn],@cell="")
 All the rows where the date is this year  =COUNTIFS([Date Column]:[Date Column], @cell <> "", [Date Column]:[Date Column], YEAR(@cell) = YEAR(TODAY()))
It is worth noting that the second formula as is will return all blank rows at the end of a sheet so using the formula below referencing a cell which should always have a value as per the formula below.
=COUNTIFS([DateColumn]:[DateColumn], @cell = "", [OtherColumn]:[OtherColumn], @cell <> "")
Hope this helps!
John

That did the trick! THX!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!