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

SUMIF CHILDREN.PNG

SUMIF CHILDREN 2.PNG

Find more posts tagged with

Finance

Formulas

Real Estate

Services

Comments

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

SUMIF CHILDREN 3.PNG

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

SUMIF CHILDREN 3.PNG

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.

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…