Sumif across Children and Grand Children

Michaël @ NDA Group

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

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

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

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

Michaël @ NDA Group

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

Michaël @ NDA Group

Dear Paul,

Thx a lot for spotting my typo mistake!

All the best!

Michaël

Andrée Starå

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

 

Paul Newcome

Happy to help! 

Paul Newcome

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