SUMIF within Parent

Hello!

I am trying to create sum Month to Date Actuals for all Leadership Development Children that have "EMEA" in Region. I will do a separate formula for JAPAC.

So far I have managed to create the below:

=SUMIF({Sheet - 01 - February Ledger Region EMEA}, FIND("EMEA", @cell) > 0, {Sheet - 01 - February Ledger Month Actuals EMEA})

However it don't know how to limit to the Leadership Development parent. Is anyone able to help?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You would need a helper column on the source sheet that pulls in the parent row data onto each child row.

=PARENT([Programme Name]@row)

Then you would reference this new helper column in your SUMIFS.

AHarper

Hi Paul,

Though I am pretty adept in Excel, I am very new to Smartsheets.

Im not sure what to do with a helper column in smartsheets and what it would be pulling. Could you take a screenshot so I can better understand?

Paul Newcome

You would just need to insert a new text/number column on the source sheet and use the above formula as a column formula. You can then hide the column to help keep the sheet clean.

AHarper

Thanks for getting back to me Paul.

I added the helper column but I'm still running into problems. The Summary Sheet cannot differentiate between the children categories (Leadership Development, Prof. Dev, etc.) in Source Data.

(eg. Summary Sheet [March]12 = $60,549. It should display $10,000. This is the result when tallying all items in Source Data - FlexLearn with JAPAC as Region. Im not sure if I used the helper column correctly with the formula. Please let me know. I appreciate your help and time on this!

=SUMIF({Sheet - 01 - February Ledger Region EMEA}, FIND("EMEA", @cell) > 0, {Sheet - 01 - February Ledger Month Actuals EMEA})

Source Data

Summary Sheet

Paul Newcome

@AHarper You need to switch over to a SUMIFS so you can include multiple range/criteria sets and then include the helper column as a range/criteria to tell the formula what to do.

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.