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.
Best Answer
-
Hi @Neville
Your criteria for this will be two things:
- Greater than or equal to Today
- 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
-
Hi @Neville
Your criteria for this will be two things:
- Greater than or equal to Today
- 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 -
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!