Sumifs formula for children rows if meet a certain criteria (using sheet reference)

Hi,

I am looking for a formula to use in my metric sheet to sum the children rows that meet a certain criteria. I have tried everything I could think of and have had no luck. I have my source sheet with the parent/children rows, and then a separate metric sheet I am using to put the formulas in and referencing my source sheet. Do I need to add some helper rows to my source sheet to do this?

I need to sum the children rows in Column A, if the criteria in the grandchildren rows (column = X

Any help is appreciated.

Thank you,

Crystal

Find more posts tagged with

Comments

Paul Newcome

You will need a helper column or two on the source sheet for this.

I would suggest one that displays the level of hierarchy using a formula such as

=COUNT(ANCESTORS([Column Name]@row))

Then you can use that as an additional range/criteria in your metrics sheet.

Parent rows will have a value of 0.

Children = 1

Grandchildren = 2

Great Grandchildren = 3

so on and so forth...

Crystal Panning

Hi Paul,

Thank you. I added a helper hierarchy row to the source sheet and used it as an additional range, but still didn't work. I need to sum the numbers in the child row, but the criteria for that sum is in the grandchildren row. Is that why it isn't working?

I need to get the sums of the numbers in column A if the rest of the criteria is met.

sum the totals in column A-child row, if column B- grandchildren row has a value of X

Any additional thoughts?

Thank you,

Crystal

Paul Newcome

My apologies. I misread your original post.

Use a helper column on the source sheet with an IF statement in it along the lines of...

=IF(CHILDREN([Column B]@row) = "X", [Column A]@row)

If you put this in the parent rows, you can use the IF to determine your criteria and populate the value from Column A if the criteria matches.

Then your Metrics sheet would just sum this helper column.

Sign in to join the conversation!

Quick Links

Trending discussions

How to prevent or recover deletion of rows
Is it possible to prevent the deletion of rows in a sheet? Specifically, in cases where a user legitimately requires editor permission to the underlying sheet and where the row cannot be locked (as continued editting of the row is required.) If not, what are some strategies for recovering a row that has been inadvertently…
Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…