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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!