COUNTIFS with Dates

I'm trying to count the number of projects in the Public Works Department that have been updated after 11/14/2021. The below formula keeps coming back as #UNPARSEABLE. Can someone help tell me what's wrong with the formula.

=COUNTIFS([LAST_UPDATE]:[LAST_UPDATE], >=DATE(2021, 11, 14)),([IMPL_DEPARTMENT]:[IMPL_DEPARTMENT],="Public Works")



Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @David French

    It looks like you have an extra parentheses. The countifs function has the syntax (range1, criteria1, range2, criteria2,etc). Notice how there are no parentheses around each range-criteria pair.

    =COUNTIFS([LAST_UPDATE]:[LAST_UPDATE], >=DATE(2021, 11, 14),[IMPL_DEPARTMENT]:[IMPL_DEPARTMENT],"Public Works")

    Your COUNTIFS formula is looking for future date updates (greater than, equal to DATE(2021, 11, 14)). Is this what you intended?

    cheers,

    Kelly

  • Thank you Kelly! I knew it was something simple that I was missing. That worked.

    Yes, the future date is intentional. I'm setting this sheet up to track data starting on 11/14/21.

    Thanks again.

    Dave

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Great! Glad it worked.

    If you would like your future tracking to be dynamic instead of hard coded, let the community know if you need help with that. For example, you could track it dynamically, based on counting all the dates that are equal to the current weeknumber.

    cheers,

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!