Use SUMIFS referencing start and end dates from another sheet

JohnTriV

Hello,

I have three sheets. The first one has four attributes (Let's call it Main):

[ID, Observation Date, Group, Count]

The second one has three (Let's call it Week):

[Week ID, From Date, To Date]

The third has all the Groups listed and the corresponding weeks where the counts will be displayed based on met criteria (Summary sheet).

I'm using SUMIFS to calculate the count if the ID matches my criteria, and the Observation Date falls between Start Date and End Date in the sheet 'Week.'

Here are my two attempts:

=SUMIFS({Count}, {Group}, "Zildj", {Observation Date}, >={From Date}, {Observation Date}, <={To Date})

=SUMIFS({Count}, {Group}, "Zildj", {Observation Date}, AND(>={From Date}, <={To Date})

I am trying to sum data from Main where the count occurred between the two dates from the Week sheet, then display everything in the Summary sheet. I am using the Week sheet to drive the 52-week calendar.

Thank you for your assistance.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

@JohnTriV You need to put the week number on the Main sheet.

=INDEX({Week Sheet Week Column}, MATCH(MAX(COLLECT({Week Sheet From Date Column}, {Week Sheet From Date Column}, @cell <= [Observation Date]@row)), {Week Sheet From Date Column}, 0))

Then you would include this new column as a range/criteria set in your SUMIFS.

=SUMIFS({Count}, {Group}, @cell = "Zildj", {New Week Column}, @cell = "Week 01")

All comments

Ape_Man819

You're on the right track with using SUMIFS, but Smartsheet formulas don't support logical operators like >= or <= directly inside the formula the way Excel does. Instead, you need to use cell references or cross-sheet references that already contain the date values you're comparing against.

Here’s how you can structure your formula correctly:

SUMIFS({Count}, {Group}, "Zildj", {Observation Date}, >= [From Date]@row, {Observation Date}, <= [To Date]@row )

Should be:

=SUMIFS(Count,Group,"Zildj",ObservationDate, > = [FromDate]@row,ObservatioDate, < =[ToDate]@row

{Count}, {Group}, and {Observation Date} are cross-sheet references to your Main sheet.
[From Date]@row and [To Date]@row refer to the current row in your Summary sheet where the formula is placed.
"Zildj" is the group you're filtering for. You can replace this with a cell reference like [Group]@row if you want it to be dynamic.
Make sure your cross-sheet references are correctly set up and named.
If you're using this formula in multiple rows (e.g., one per week), ensure [From Date] and [To Date] are columns in your Summary sheet that pull from the Week sheet.

Hope this helps.

JohnTriV

Hey, thank you! The From and To Dates though are not on the reference sheet; it's on another one. I think I'm close, but still unparseable:

SUMIFS({Count}, {Group}, "Zildj", {Observation Date}, >= [From Date]@row, {Observation Date}, <= [To Date]@row )

The bold conditions are cross-referenced as well. I tried this:

SUMIFS({Count}, {Group}, "Zildj", {Observation Date}, >= {From Date}@row, {Observation Date}, <= {To Date}@row )

Where:

Count, group and observation date are from Main

From Date and To Date are from Week

All these are going to be displayed in a Summary sheet.

Paul Newcome

@JohnTriV You need to put the week number on the Main sheet.

=INDEX({Week Sheet Week Column}, MATCH(MAX(COLLECT({Week Sheet From Date Column}, {Week Sheet From Date Column}, @cell <= [Observation Date]@row)), {Week Sheet From Date Column}, 0))

Then you would include this new column as a range/criteria set in your SUMIFS.

=SUMIFS({Count}, {Group}, @cell = "Zildj", {New Week Column}, @cell = "Week 01")

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.