Formula with SUM, COLLECT & CHILDREN

I have Sheet 1 (uploaded as Sheet 1 Overview) whose overview is shown as having hierarchy where some of the parent rows (in the Process column) contain the word "Hours". Within the expansion of the "Hours" parent row (uploaded as Sheet 1 Section A and Sheet 1 Section B), I have multiple rows that contain Terms in the Term column and various partners in remaining Columns. The intersection of each Term/Partner column contains a cell with an aggregated number of hours pulling from different sheets within a process. I then have Sheet 2 (uploaded as Sheet 2 Overview) where I would like to create a formula in the matrix of all cells that would summarize data collected from children rows of each parent containing "Hours" in Sheet 1, my matching the Terms in term@row and partner name (i.e., AVE NOR) in columns.
My current matric formula in Sheet 2 Overview is with all formula parts pulling from Sheet 1 Overview:
=SUM(COLLECT(CHILDREN({AVE NOR}@row), CHILDREN({BPO Process Tracker Payroll Process}@row), CONTAINS("Hours"),CHILDREN({BPO Process Tracker Payroll Term}@row), Term@row, 1))
but I am getting an #UNPARSEABLE error and I am having trouble defining the issue or another formula method to accomplish my end goal. Any help would be appreciated. Thank you!
Answers
-
Hi @Gina Smith
Thanks for the detailed explanation. One quick note — using
{range name}@row
inside cross-sheet formulas likeCOLLECT()
orCHILDREN()
can lead to errors, especially when the syntax tries to apply row-based logic across sheets.For cross-sheet references, a better approach is to use this kind of structure:
=SUM(COLLECT({Range to Sum}, {Condition Range 1}, Term@row, {Condition Range 2}, "Hours"))
Or use SUMIFS, if your conditions are straightforward:
=SUMIFS({Range to Sum}, {Condition Range 1}, Term@row, {Condition Range 2}, "Hours")
Also, since
CHILDREN({range})
doesn't work across sheets, create a helper column in your source sheet (e.g., "Parent") using a formula like=PARENT([Column to identify parent]@row)
. This allows you to simulate parent-child filtering.Then you can use formulas like:
=SUM(COLLECT({BPO Process Tracker Payroll AVE NOR}, {BPO Process Tracker Payroll Parent}, CONTAINS("Hours", @cell), {BPO Process Tracker Payroll Term}, Term@row))
or:
=SUMIFS({BPO Process Tracker Payroll AVE NOR}, {BPO Process Tracker Payroll Parent}, CONTAINS("Hours", @cell), {BPO Process Tracker Payroll Term}, Term@row)
This method keeps things clean, avoids unsupported syntax, and works well for cross-sheet aggregation. Let me know if you'd like help adjusting your current formula.
-
Thank you, jmyzk_cloudsmart_jp. This is very helpful; however, I am having some issues applying it.
Here is the extra column I added to my Sheet 1 Overview
Here are two different attempts to add the SUMIFS formula to Sheet 2 Overview, both of which are still throwing #UNPARSEABLE errors. I have never used @cell before. The other parts of the formula I use regularly so I assume my error is related to the @cell function which is why I tried modifying the formula to the method Smartsheet Help gave me.
Are you able to see what it is I am doing wrong in the formula?
Thanks,
-
In your first formula, the {range}@cell format is not permitted in Smartsheet.
I updated my demo solution to use the same CONTAINS condition as yours, instead of just checking if it contains "Hours" but "Faculty Payroll Contracts - Hours." I found that the updated formula also works.
I checked your second formula and found the formula structure has no problem.
Your second formula
My formula
=SUMIFS({BPO Process Tracker Payroll AVE NOR}, {BPO Process Tracker Payroll Parent}, CONTAINS("Faculty Payroll Cotracts - Hours", @cell), {BPO Process Tracker Payroll Term}, Term@row)
So, a possible cause is that your range is not set correctly, such as referencing a cell, not a whole column.
You can check the reference by the Manage Reference menu. Right-click anywhere in your sheet and choose the menu.
-
The issue with the second formula is simply one too many closing parenthesis at the end.
Help Article Resources
Categories
Check out the Formula Handbook template!