countifs formula to sum not blank dates

edited 12/09/19 in Using Smartsheet
04/30/19 Edited 12/09/19

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

Popular Tags:

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try something like this...

     

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

  • Mike WildayMike Wilday ✭✭✭✭✭

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

  • Paul NewcomePaul 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)) + ""

  • 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 WildayMike 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! 

    laughyescool

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

  • Paul NewcomePaul 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 NewcomePaul Newcome ✭✭✭✭✭

    Kelly,

     

    Always happy to help! yessmiley

Sign In or Register to comment.