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
Check out the Formula Handbook template!