Past Due Formula via Date Column in references source sheet

Options

Hello,

I have the below formula to show total count of projects that are less than or equal to today days and the due date.

=COUNTIFS({Estimator}, [Assigned To]@row, {Assigned To}, [Assigned To]@row, {Quote Due Date}, <=TODAY(), {Quote Sent Date}, "")

How can this be tweak to show, Total Count Projects Past Their Due Date?

The Quote due date is coming in from another source sheet that is a date column?

Feels like I have been staring at the symbol actions (eg <= and >= ) for ages and now I am starting to get confused what they actually mean with what I am trying to do lol.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Ty Wickliffe

    Are you just looking to remove out any of the "assigned to" criteria so it shows the totals?

    COUNTIFS formulas are great because of their repetitive structure. In this instance all you need to do is remove out the first two ranges and criteria so you're only looking at the due date (and if the Sent Date is blank).


    Try this:

    =COUNTIFS({Quote Due Date}, <=TODAY(), {Quote Sent Date}, "")


    This count will include TODAY's date because of the = sign, so if you want to only have dates in the past you can do this:

    =COUNTIFS({Quote Due Date}, <TODAY(), {Quote Sent Date}, "")

    This says, count the cells in the range "Quote Due Date" that are less than Today (or in the past of Today), but only if the Quote Sent Date is blank.

    Let me know if this is what you were looking to do!

    Cheers,

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Ty Wickliffe 

    Hope you are fine, could you please supply a screenshot for your sheets  (Delete/replace any confidential/sensitive information before sharing)

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Ty Wickliffe

    Are you just looking to remove out any of the "assigned to" criteria so it shows the totals?

    COUNTIFS formulas are great because of their repetitive structure. In this instance all you need to do is remove out the first two ranges and criteria so you're only looking at the due date (and if the Sent Date is blank).


    Try this:

    =COUNTIFS({Quote Due Date}, <=TODAY(), {Quote Sent Date}, "")


    This count will include TODAY's date because of the = sign, so if you want to only have dates in the past you can do this:

    =COUNTIFS({Quote Due Date}, <TODAY(), {Quote Sent Date}, "")

    This says, count the cells in the range "Quote Due Date" that are less than Today (or in the past of Today), but only if the Quote Sent Date is blank.

    Let me know if this is what you were looking to do!

    Cheers,

    Genevieve

  • Ty Wickliffe
    Options

    Hi @Bassam.M Khalil ,


    Thanks for reaching out so quick. @Genevieve P managed to answer my question.

    @Genevieve P - your formula worked a treat. Thanks again! 😁

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem! Glad it worked for you 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!