Hi everyone,
I’m trying to calculate the total hours worked by cost code across two Smartsheet sheets, but I’m hitting a formula limit on my primary sheet, where I track time entries.
Setup:
- I have a timeclock sheet where employees log their hours, and each entry includes one or more cost codes in a single cell to indicate the type of work done.
- I have a forecasting sheet where I want to pull the total hours for each cost code to help calculate remaining time for various tasks.
Current Approach:
I initially created helper columns in my timeclock sheet to extract and sum hours by cost code, but I’ve reached Smartsheet’s formula limit.
Existing Formula:
Here’s the formula I’m using in my forecasting sheet:
=ROUNDUP(IF(COUNT(ANCESTORS(Description@row)) = 0,
(SUMIFS({Time Clock Master CCT1}, {Time Clock Master Sheet Range 2}, Description@row) +
SUMIFS({Time Clock Master CCT2}, {Time Clock Master Sheet Range 2}, Description@row)),
(SUMIFS({Time Clock Master CCT1}, {Time Clock Master Sheet Range 2}, PARENT(Description@row),
{Time Clock Master CC1}, CONTAINS(Crew@row, @cell)) +
SUMIFS({Time Clock Master CCT2}, {Time Clock Master Sheet Range 2}, PARENT(Description@row),
{Time Clock Master CC2}, CONTAINS(Crew@row, @cell)))), 0)
This formula checks if the row has ancestors (indicating a higher-level task), then sums up hours based on cost codes in different columns, with some conditions based on crew assignments.
Question:
Is there a way to optimize this formula so I don’t hit the formula limit? Or is there another way to pull these totals into my forecasting sheet without using helper columns?
Thanks in advance! Any insights would be greatly appreciated.
Here is the formula that I am currently using in my CC1/2 columns :
=IFERROR(MID([Cost Codes]@row, FIND("§", SUBSTITUTE([Cost Codes]@row, "[", "§", 2)), FIND("§", SUBSTITUTE([Cost Codes]@row, "(", "§", 2)) - FIND("§", SUBSTITUTE([Cost Codes]@row, "[", "§", 2)) - 1), "")
