Best Of

My concern though is what if you have a count of days that = 61-90. It currently would have it blank

Just cover the rest with a >=60

=IF(ISBLANK([Expire Date]@row), "", IF([Expire Date]@row < TODAY(+30), "Red", IF([Expire Date]@row < TODAY(+60), "Yellow", IF([Expire Date]@row >= TODAY(+60), "Green"))))

@WtaylorW do you have DataMesh? That would make it easy. However, you can set up another sheet to make this happen. I'll show you how to do this with one employee name -- just repeat for each column.

Create a workflow that's set up to copy a row to another sheet (Transfer Sheet 1) when the following is true:

• Name column is empty
• Employee Name 1 column is not empty

In Transfer Sheet 1, reflect the Employee Name 1 column into the Name column using this column formula:

=[Employee Name 1]

Set up a workflow in this sheet to immediately copy any added row back to your initial sheet. Test that to ensure it copies back with the Name column populated.

Troubleshoot the above to get this working. Now, the simplest way to get the other names to populate as a line is to repeat the above with a new transfer sheet for each name. You might be able to hack a different methodology, but there are multiple reasons this would likely backfire.

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()))