Help with Summing Hours Based on Cost Codes Across Sheets

Nathan_FOF

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:

1. 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.
2. 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), "")

MedaUser

Hi Nathan,

It's always frustrating hitting the formula limit within a cell. I tend to work toward eliminating 1 formula command at a time. My suggestion would be to create an Ancestors column in your "Existing Formula" sheet that will calculate the ancestor count for you (as a helper-type column). This should allow you to eliminate the COUNT and ANCESTORS formulas by rolling straight into the IF statement:

=ROUNDUP(IF(AncestorsHelperColumn@row = 0, …

Travis C, PMP

Smartsheet Leader with 5+ years of SS experience

If you were happy with my answer, please upvote and mark my response as answered.