SUMIFS with Parent, children, grandchildren

jb@59069

I have client sheets that total time based on form entry, others that total expenses based on form entry. Summaries of these values are used in client reports.

I have new formulas with the help of Smartsheet support, that will make adding/editing formulas much more efficient than the various workarounds I've been doing. These new formulas seem to be working for one client where I only need to SUMIFS parents and children.

However, another client is more complicated and I can't seem to get the formula to work. There needs to be an additional criteria set for checking the value of column "Phase (I, II, FG or ST)"

GKC_Time_Columns_05Mar2019.png shows the columns I'm pulling data from (except the "Client Billable?" checkbox column.

GKC_Time_Formula_05Mar2019.png shows the formula pulled from the other client sheet but modified, not working.

GKC_Time_Data_05Mar2019.png shows two sample entries that should be summed.

GKC_Time_Result_05Mar2019.png shows the result. The cell at Jun 15, 2019, Row 4 should have a value of "1". Jun 15, 2019, Row 62 is correct for the Phase II entry using the old formula which is an incredibly labor intense update process with 180 formulas per column in this sheet alone.

I hope this makes sense and someone can point out where I need to modify the formula.

Thanks.

Eric M Oliveira

Hello,

 

Happy to help! There are a couple of expressions written within the formula that does not seem to align with the column values of the sheet. Also, the Time Worked column isn't in the above screenshots so it's hard to tell if there are any values to be summed if all meet the criteria. In addition, the Client Billable column seems to be missing from the screenshots above which is a criterion of the formula. 

 

As a first test, often formulas seem to not function as desired when there are no values within the cells as displayed in these screenshots. Please fill in all the cells of a single row with the ideal values of the row. Does the formula function as desired?

 

If not, one of the below may be the issue. 

 

1. Currently, the formula contains a CHILDREN function with the reference to the GKC column at the row level ($GKC@row). However, the GKC column at the row of where the formula is located isn't a Parent row and doesn't have any Children. 

 

2. From my understanding, the newly added criteria is the Phase column. Based on how the formula is written the Phase column would have to equal the value of the Parent row of the GKC column at the row level, which is "Branding". I do not believe this is desired. From my understanding, it's meant to the parent of row 3, the value Phase 1. 

 

It may be best to have the Phase column reference it's own parent, placing the Phase value in the Parent of the Phase column. Or if you truly desired to reference the GKC column you may want to reference the Parent of row 3 and not the value at row. 

 

Please let us know if you have any questions on the above. 

 

If the issue continues after performing the above I strongly suggest connecting with us via our support form so that we can connect with you via phone. That way the issue is resolved quickly. https://help.smartsheet.com/contact

 

Have a wonderful day,

 

Eric  -  Smartsheet Technical Support