SUMIF cell values land within a given week

Hey,

I've gotten some fair formula experience over the last year (mostly through googling and this searching this forum) but hit a brick wall with my latest ambition…

I have a smartsheet in Gantt mode tracking events and sizes relating to the event. I'd like to be able to use a date picker in the summary tab to pick a date, then if an event lands within the calendar week (mon to sun) of the date picked, sum up the sizes.

(eg below, if the Date Picker in the summary is 26/03/26, row 289 occurs on the Monday of that week, I'd like 'Sum If' box to add the value(s) in TB/week)

I've tried a couple of ways, I can get it to work if the date picker matches the event start/end date, but cant get the next step of if the date fits the range.

So far, 'Sum If' in the summary works as '=SUMIFS([TB /week]:[TB /week], Start:Start, <=[Date Picker]#, Finish:Finish, >=[Date Picker]#)'

I also tried with an 'In week' tickbox column, that ticks if the event is in the date picker range, but struggled to get further.

Could any formula gurus offer any advice? Is it even possible?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Give this a try:

=SUMIFS([TB /week]:[TB /week], Start:Start, @cell <= ([Date Picker]# + (1 - WEEKDAY([Date Picker]#))) + IF(WEEKDAY([Date Picker]#) > 1, 7, 0), Finish:Finish, @cell >= [Date Picker]# + (2 - WEEKDAY([Date Picker]#)) - IF(WEEKDAY([Date Picker]#) < 2, 7, 0))

All comments

Paul Newcome

Give this a try:

rkck

Wow, nailed it. Thank you!

I'll spend the day breaking this down to understand it properly. Would I be right to say it looks like it's comparing if the date from Date Picker is <=/ >= WEEKDAY in a 7 day range?

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.