countifs formula to sum not blank dates
Hello ~ I'm new to the community and working with the COUNTIFS formula. Help would be much appreciated if you can see the error of my ways. What I want to do seems simple - I have a "Date Completed" column, defined as a Date type, and want to count how many times it's been entered in the column. I saw a thread earlier that addressed a similar topic, but I've so far been unable to get it to work. Here's the formula I'm working with:
=COUNTIFS([Date Completed]:[Date Completed], IFERROR(YEAR(@cell), 0) >= 2019)
I'd like it to be more generic, using a not isempty, but so far haven't been able to get it to work. The formula is expecting a #DATE EXPECTED (presumably since the column is a Date type) - is there a way to do this?
Thank you
Comments
-
-
Try this. It will find every blank cell in the date completed column.
=Countifs([date completed]:[date completed], ISBLANK(@cell))
-
Thank you, Paul, for you comment. It's giving a #DATE EXPECTED error. I think I need the IFERROR to catch that, but so far, haven't solved it.
-
Hi Mike, and thank you too, for you comment. It's also giving a #DATE EXPECTED error. I *think* I need to use an ISERROR to pick up and catch the error, but so far, no luck. I'm also working with trying a not(isblank(@cell))
-
The cell you are putting the formula into must be in a date type column. Try this...
=COUNTIFS([date completed]:[date completed], ISDATE(@cell)) + ""
-
OK Paul, first, you're brilliant and thank you, that worked. Now second, I'm trying to understand why? What does the + "" do? How would you read this in English? Count the row if the date completed is a valid date anywhere in the column? but what's the + "" for?
And thank you again!
-
The +"" turns the result of the function into a text... instead of requiring it to be a date. The formula will no longer expect to produce a date output. It is pretty genius. Good job Paul!
-
Ah - Thanks, Mike, for the clarification - and thanks Paul ~ that is a great workaround.
-
Thanks Mike for going ahead with the explanation.
I use the + "" a lot in date, checkbox, and symbol fields. One of my most typical uses (while not exactly the same but very close no less) is when I build out a date table.
We can start a project whenever we want so long as certain deliverables are met before a specific date. My people don't like blank cells when looking at projected dates, so for that first set of deliverables, the Projected Start is - and the Projected Finish is the Friday before Week 1.
If you just type a - into a date type cell, you get the error. But if you turn it into "text" it will be displayed. So the Projected Start for Week 0 is actually
="-"
A lot of walls have felt the wrath of my forehead getting to this relatively simple solution, so this one tends to stick with me a bit. HAHAHA
-
Kelly,
Always happy to help!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives