SUMIF row is in the next 5 working days

Hello,

I am trying to create a formula that counts the total size (EQA) of the jobs that are due in the next 5 working days.

I am using this formula but it's coming up with 0

=SUMIFS(EQA:EQA, [Delivery Date]:[Delivery Date], ISDATE(1), [Delivery Date]:[Delivery Date], NETWORKDAYS(TODAY(), TODAY(7), 0))

Any help on this would be greatly appreciated because I need to do a few of these calculations.

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Neville

    Your criteria for this will be two things:

    1. Greater than or equal to Today
    2. Less than or equal to 5 Working Days from now

    This means that for your second criteria, instead of using the NETWORKDAYS function to find a number, you actually want to use the WORKDAY Function to find a date, like so:

    WORKDAY(TODAY(), 5)

    This looks for 5 working days from Today's date.

    Try this formula instead:

    =SUMIFS(EQA:EQA, [Delivery Date]:[Delivery Date], @cell >=TODAY(), [Delivery Date]:[Delivery Date], @cell <= WORKDAY(TODAY(), 5))


    Cheers,

    Genevieve

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

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Neville

    Your criteria for this will be two things:

    1. Greater than or equal to Today
    2. Less than or equal to 5 Working Days from now

    This means that for your second criteria, instead of using the NETWORKDAYS function to find a number, you actually want to use the WORKDAY Function to find a date, like so:

    WORKDAY(TODAY(), 5)

    This looks for 5 working days from Today's date.

    Try this formula instead:

    =SUMIFS(EQA:EQA, [Delivery Date]:[Delivery Date], @cell >=TODAY(), [Delivery Date]:[Delivery Date], @cell <= WORKDAY(TODAY(), 5))


    Cheers,

    Genevieve

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

  • Neville
    Neville ✭✭

    Hi @Genevieve P. ,

    Thank you so much, you didn't just answer my question you actually helped me understand this so much better!.

    Very much appreciated.

    Thanks :)

    Neville

  • No problem at all! I'm glad I could help. 🙂

    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!