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!

Sheet 1 Overview.jpg Sheet 1 Section A.jpg Sheet 1 Section B.jpg Sheet 2 Overview.jpg

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @Gina Smith

    Thanks for the detailed explanation. One quick note — using {range name}@row inside cross-sheet formulas like COLLECT() or CHILDREN() 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.

    https://app.smartsheet.com/b/publish?EQBCT=4addaef601ed4be1a541aed3bc70ba17

    image.png

    https://app.smartsheet.com/b/publish?EQBCT=e89e07b4a8bb4f13a41bfb7dce541eb8

    image.png

    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.

  • Gina Smith
    Gina Smith ✭✭✭✭
    edited 05/14/25

    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

    Parent Column Added to Sheet 1 Overview.jpg

    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.

    Formula 1 Sheet 2 Overview.jpg Formula 2 Sheet 2 Overview.jpg

    Are you able to see what it is I am doing wrong in the formula?

    Thanks,

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    @Gina Smith

    In your first formula, the {range}@cell format is not permitted in Smartsheet.

    image.png

    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.

    image.png

    I checked your second formula and found the formula structure has no problem.

    Your second formula

    image.png

    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.

    image.png

    image.png

    image.png

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!