SUMIF within Parent

AHarper

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?

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.