SUMIFS Based on Parent and Children Criteria

Allison_S

Hi there!

I am trying to make a master reporting sheet that is just a sum of another source sheet based on specific criteria of parent and child rows. It's a two step SUMIFS that I can't seem to get straight. It would need to be a sum "grand total count", if hierarchy 0 month = specified month and hierarchy 1 matches specified label. I should add that my source sheet is fed from the Pivot app, so months/rows are added hourly based on labels used. So the formula needs to be "rolling" to incorporate new items added (months, labels). Any help would be greatly appreciated, Images attached, closets I can get with formulas are =SUMIFS({Label 1-72 Grand Total Count}, {Label 1-72 Month & Label}, CONTAINS([Month #]1, {Label 1-72 Month & Label}), {Label 1-72 Month & Label}, Label@row) that returns a value of 0 every time. Thank you!

Alejandra

Hi Allison,

I was able to get this to work by inserting the Month # column in the source sheet. I also placed this formula in the Month # column to return the parent row value: =PARENT([Month and Label]@row)

Your new formula could looking something like this:

=SUMIFS({Gand Total Count}, {Month #}, "1", {Month & Label}, Label@row)