SUMIFS Based on Parent and Children Criteria

Allison_S ✭✭✭
edited 12/09/19 in Formulas and Functions

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!

Master Reporting Sheet.png

Information Source Sheet.png


  • Alejandra
    Alejandra Employee

    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)




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!