COUNTIF with dates pulled from cells?

Options
David Acord
David Acord ✭✭
edited 10/29/21 in Formulas and Functions

Hello!

I'm trying to run a count of the number of entries on a sheet, where the "needs analysis assigned date" is greater than 5 days prior to the "date requestor contacted" date for that same row of data.

Example:


Row 1:

Needs analysis assigned date: 11/1/2022

Date Requestor contacted: 11/2/2022

The date difference only being 1 day, this should not be counted.


However, for:

Row 2:

Needs analysis assigned date: 11/1/2022

Date Requestor contacted: 11/7/2022

This should be counted as it's been 6 days.


My formula is below, and it's #UNPARSEABLE. can anyone help?

=COUNTIF([Date Requestor Contacted]:[Date Requestor Contacted], <=[Needs analysis assigned date](-5)@cell)

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    edited 11/01/21 Answer ✓
    Options

    Hi @David Acord

    I'm glad this is on the right track for you! Good point about blank dates. Yes, lets add in another statement to make sure this doesn't happen:

    =IF(OR([Date Requestor Contacted]@row = "", [Needs analysis assigned date]@row = ""), 0, IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0))

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Options

    Hi @David Acord

    The way I would do this is to have a helper checkbox column hidden on the sheet with a Column Formula applied that would check each row for these conditions. If it's true, it would check the box. Then your COUNTIF can simply count how many checkboxes there are!


    Try something like this as a column formula:

    =IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0)


    Then your COUNTIF would be:

    =COUNTIF([Helper Column]:[Helper Column], 1)


    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • David Acord
    Options

    Fantastic! Thanks @Genevieve P. - this is definitely working. I've noticed a hiccup (at least for me) - this will also check the box if there's no date entered in either of those date columns, such as if the row has incomplete data. Do you have any suggestions on how to not count those/not check the box until those two dates are filled in?

  • Genevieve P.
    Genevieve P. Employee
    edited 11/01/21 Answer ✓
    Options

    Hi @David Acord

    I'm glad this is on the right track for you! Good point about blank dates. Yes, lets add in another statement to make sure this doesn't happen:

    =IF(OR([Date Requestor Contacted]@row = "", [Needs analysis assigned date]@row = ""), 0, IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0))

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • David Acord
    Options

    That did it! Thanks so much!

  • Genevieve P.
    Options

    Wonderful! 🙂 No problem at all.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!