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?

Find more posts tagged with

Accepted answers

Nick Korna

I think this should do what you're after:

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

All comments

Paul Newcome

It would be

WORKDAY(TODAY(), -30)

Jeff Reisman

@Jtbeck

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:

Nick Korna

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! 😊

Jeff Reisman

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

Nick Korna

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

Nick Korna

I think this should do what you're after:

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

Jeff Reisman

@Nick Korna I think you got it! 😉

https://community.smartsheet.com/discussion/comment/369680#Comment_369680

Nick Korna

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.