Best Of

Hi @Dakota Haeffner,

To solve your issue with separating totals by week based on your existing setup in Smartsheet, and taking into account your usage of Data Shuttle for periodic data updates, here are a few strategies you can consider:

1. Enhanced SUMIFS with Multiple Week Filtering

Since you want to aggregate data by specific weeks (e.g., weeks 1 and 2, or weeks 3 and 4), a straightforward SUMIFS approach with a single criterion won't suffice. You'll need a more complex formula to include multiple week numbers as criteria for summing. However, Smartsheet's formula capabilities are somewhat limited compared to Excel, and it doesn't support array formulas or directly filtering by multiple conditions in the same column in a SUMIF/SUMIFS function.

2. Helper Column for Week Groupings

One scalable solution is to use a "Helper Column" in your source sheets that groups weeks according to your requirements. For example:

• Add a new column called "Week Group" or similar.
• Use a formula to assign a group identifier based on the week number. This could be as simple as grouping weeks into pairs (e.g., "1-2", "3-4") or any other grouping logic that suits your analysis needs.
• Your formula in this helper column might look like this, depending on your exact grouping logic:

=IF(OR([Week Number]@row = 1, [Week Number]@row = 2), "1-2",
 IF(OR([Week Number]@row = 3, [Week Number]@row = 4), "3-4", "5"))

This example assumes weeks are grouped as "1-2", "3-4", and "5" for simplicity.

3. Modified SUMIFS Using Helper Column

With the "Week Group" column in place, you can modify your SUMIFS formula to sum based on the group identifier instead of individual weeks:

=SUMIFS([Total Column Name], [Week Group Column Name], "[Group Identifier]")

Replace [Total Column Name], [Week Group Column Name], and [Group Identifier] with the actual names of your columns and the identifier of the week group you want to sum.

4. Dashboard Widgets or Reports for Dynamic Filtering

For a more dynamic and interactive solution, consider using Smartsheet Dashboards or Reports:

• Dashboards: You can create Metric Widgets to display totals based on specific criteria, including your week groupings. While Dashboards offer great visualizations, they might not offer the granular control you're looking for in filtering by week groups directly.
• Reports: Create a report to aggregate data across your sheets. You can include the "Week Group" column as a filter criterion in the report settings, allowing you to dynamically select which week groups to include in your totals. Reports can be very powerful for this type of analysis since they can pull in data from multiple sheets and allow for easy filtering and aggregation.

5. Automating with Data Shuttle

Ensure that your Data Shuttle configurations are set up to update the source sheets regularly, preserving the integrity of your "Week Number" and "Week Group" calculations with each data refresh.

These strategies combined should allow you to aggregate your employee utilization totals by week more effectively, providing the flexibility to filter and analyze the data as needed. If you encounter specific limitations or need further customization, consider integrating with external tools or services that can process the data outside of Smartsheet before importing the aggregated results back into your sheets.

Hi @DJB - glad you got back to this then! When you create a column formula, just remember you can only do MyColumn@row references (vs MyColumn12 if you want the 12th row).

If you need to reference a value in a single row in a column formula, you can create an entry in the Summary tab (Example: MyEntry) that points to the individual cell in the sheet (i.e. =MyColumn12). In your column formula, you can point to that Summary tab entry using the hashtag format, i.e.:

=IF(OtherColumn@row=MyEntry#, "True", "False")

Try thinking of dates as numbers where today is zero, tomorrow is one and yesterday is negative one. If you think about it that way, then what you are trying to accomplish is counting dates that are less than (today - 90 days).

=COUNTIFS(Completed:Completed, @cell <= TODAY(-91))

Ah. Ok. That's why you are getting unexpected results. Your formula is counting future dates. Try this instead...

=COUNTIFS(Completed:Completed, AND(@cell >= TODAY(-30), @cell <= TODAY()))

Hi @DJB there's currently not a great way to reference the formulas -- I actually use the API and Python code to do something similar. Question, however -- can you use column formulas instead of the individual drag/copy method? Here's some info on column formulas (they make life a lot easier, but may take a little bit to figure out):

Use column formulas to apply calculations to all rows in a sheet | Smartsheet Learning Center

If you create the reminder using the automation work flows, there is a section to customize the message.

At the end of this is the Message Includes option.

If you select the “message only” option, the link to the sheet won’t be included.