Hello, I have the following formula that works but I want to adjust it to working days only:

✭✭

=SUMIFS({QTY produced}, {completed date}, <=TODAY(), {completed date}, >=TODAY(-30), {element}, ([description reference]@row))

This formula is pulling completed dates from another sheet. I am wanting the formula above to only count quantities 30 working days from today's date. Is there a way to modify this formula to include only working days? I think that the sheet is counting weekends at this moment. I updated the properties of the sheet to specify Mon-Fri, but not sure how to implement "WORKDAY" into the formula. Can you please advise?

• ✭✭✭✭✭✭

I think this should do what you're after:

=SUMIFS({QTY produced}, {Completed Date}, >=WORKDAY(TODAY(), -30), {Completed Date}, <=TODAY(), {Element}, [Description reference]@row)

• ✭✭✭✭✭✭

It would be

WORKDAY(TODAY(), -30)

• ✭✭✭✭✭✭

Try this:

=SUMIFS({QTY produced}, {completed date}, <=TODAY(), {completed date}, >=WORKDAY(TODAY(), -30), {element}, ([description reference]@row))

This will give you the date 30 actual working days ago:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

Hi @Jtbeck,

I've not been able to get the WORKDAY formula to play well inside the SUMIFS, so I would suggest you use a helper column on your production data. For example, using the below for a checkbox column:

=IF(AND([Completed Date]@row >= WORKDAY(TODAY(), -30), [Completed Date]@row <= TODAY()), 1, 0)

This would be ticked for any production records in the last 30 workdays and up to/including today.

This would then enable you to use a shorter formula for calculating the amount made against what I assume if a list of part numbers/descriptions:

=SUMIFS({QTY produced}, {Helper ticked}, 1, {Element}, [Description reference]@row)

Example:

Data:

Output:

Hope this makes sense and helps - if you've any questions etc. then just post! 😊

• ✭✭✭✭✭✭

@Nick Korna What issues were having with WORKDAY inside a SUMIFS? Seems to work well enough:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
edited 03/30/23

You are correct - now let me see if I can get the formula to dodge the helper column correct. 😀

• ✭✭✭✭✭✭

I think this should do what you're after:

=SUMIFS({QTY produced}, {Completed Date}, >=WORKDAY(TODAY(), -30), {Completed Date}, <=TODAY(), {Element}, [Description reference]@row)

• ✭✭✭✭✭✭

@Nick Korna I think you got it! 😉

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

@Jeff Reisman - my bad, I totally skipped that post! 🤣

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!