Sumif across Children and Grand Children

Dear Community,

I am trying to SUMIF accross all Children and Grand Children but unfortunately retrieving an #UNPARSEABLE value.

Here are the formulas I have been trying:

 

SUMIF($[Payment Status]5:$[Payment Status]50), "Received", [2019 Wk 02]5:[2019 Wk 02]50))

=> result to #UNPARSEABLE

 

SUMIF(CHILDREN($[Payment Status]27), "Received", CHILDREN([2019 Wk 01]27)

=> result will not sum the grand children but only the children, which is not what I am looking for.

 

Is there a possibility to sumif across the whole column regardless Parent / Children / Grand Children ?



Thx a lot for the help

SUMIF CHILDREN.PNG

SUMIF CHILDREN 2.PNG

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Try this.

    SUMIF([Payment Status]:[Payment Status]), "Received", [2019 Wk 02]:[2019 Wk 02]))

    Did it work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree's response is correct.

     

    As a note though... Your first formula (the one not using the CHILDREN function) was returning #UNPARESEABLE because you have an extra closing parenthesis on the end.

  • Michaël @ NDA Group
    edited 05/23/19

    Dear Andrée,

    Thank you for your response, but it does actually also return #unparseable. This cannot work since the cells I am willing to count are only under Row5, and I am returning the result in the Row3 of the same Col.

    Also, I wish to keep the first the "range" fixed and keeping the possibility to increment the "[sum_range]" So I can populate all the weeks of the year and on and on for future years, but it doesn't seems to work either.

    =SUMIF($[Payment Status]27:$[Payment Status]50), "Received", [2019 Wk 02]27:[2019 Wk 02]50))

     

    Dear Paul,

    Thank you as well for your response, My previous formula is actually a wrong copy paste, but there was no typo issue in the sheet.

    Full formula:

    =IF(SUMIF(CHILDREN($[Payment Status]27), "Received", CHILDREN([2019 Wk 01]27)) = 0, "", SUMIF(CHILDREN($[Payment Status]27), "Received", CHILDREN([2019 Wk 01]27)))

    This formula works, however this will only return me "" as it will only search the Children and not the Grand Children... So I still can't retrieve the sum of "Received" payments.

     

    If any of you have other ideas, I would be glad to discussing it further,

    Thx,

    Michaël

    SUMIF CHILDREN 3.PNG

  • Michaël @ NDA Group
    edited 05/23/19

    Dear Andrée,

    This is indeed working, my mistake was as well a typo issue spotted by Paul below...



    Also, I am trying to have the possibility to increment the [sum range] over all the 52 weeks column of the year, and then on and on, for future years to have a rolling system. But it does not seems to increment. Am I missing something?



    =SUMIF($[Payment Status]:$[Payment Status], "Received", [2019 Wk 02]:[2019 Wk 02])

    to be incremented automatically (while dragging right) to:

    =SUMIF($[Payment Status]:$[Payment Status], "Received", [2019 Wk 03]:[2019 Wk 03])



    All the best!

    Michaël

    SUMIF CHILDREN 3.PNG

  • Michaël @ NDA Group
    edited 05/23/19

    Dear Paul,

    Thx a lot for spotting my typo mistake!



    All the best!

    Michaël

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 05/23/19

    Excellent!

    Happy to help!

    For all weeks.

    The sum range has to be the same length as the criteria range. You'd need to use a SUMIF formula for each week instead and add those together.

    Try something like this and continue the pattern for all weeks.

    =SUMIF([Payment Status]:[Payment Status]; "Received"; [2019 Wk 01]:[2019 Wk 01]) + SUMIF([Payment Status]:[Payment Status]; "Received"; [2019 Wk 02]:[2019 Wk 02])

    The same version but with the below changes for your and others convenience.

    =SUMIF([Payment Status]:[Payment Status], "Received", [2019 Wk 01]:[2019 Wk 01]) + SUMIF([Payment Status]:[Payment Status], "Received", [2019 Wk 02]:[2019 Wk 02])

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Best,

    Andrée

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you referring to dragfilling? If so, it should automatically update the range as you go column by column when dragfilling.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!