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!

Find more posts tagged with

Accepted answers

All comments

jmyzk_cloudsmart_jp

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

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

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

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.

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

Thanks,

jmyzk_cloudsmart_jp

@Gina Smith

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.

Paul Newcome

The issue with the second formula is simply one too many closing parenthesis at the end.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.