countifs formula to sum not blank dates

Kelly M

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

Paul Newcome

Try something like this...

 

=COUNTIFS([date completed]:[date completed], ISDATE(@cell)) 

Mike Wilday

Try this. It will find every blank cell in the date completed column. 

=Countifs([date completed]:[date completed], ISBLANK(@cell))

Kelly M

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.

Kelly M

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))

Paul Newcome

The cell you are putting the formula into must be in a date type column. Try this...

 

=COUNTIFS([date completed]:[date completed], ISDATE(@cell)) + ""

Kelly M

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!

Mike Wilday

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! 

Kelly M

Ah - Thanks, Mike, for the clarification - and thanks Paul ~ that is a great workaround.

Paul Newcome

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

Paul Newcome

Kelly,

 

Always happy to help!