Sumifs with range

Jean Kim

Hi,

Refer to below, trying to get total sum of certain items.

Scenario : Sum numbers from the column, Color, if the column, Fruit is "Apple".

I tried below, but returning error. Please help.

=SUMIFS([color]99:[color]104), fruit99:fruit104, "Apple")

Andrée Starå

Hi Jean Kim,

You have a Parenthesis to many, and you don't need to use Square Brackets.

Square Brackets are only needed when the column name includes a number, space, or a special character.

Try something like this.

=SUMIFS(color99:color104, Fruit99:Fruit104, "Apple")

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Jean Kim

Yes,

Try something like this.

=SUMIFS(CHILDREN(color99), CHILDREN(Fruit99), "Apple")

or

=SUMIFS(CHILDREN(color@row), CHILDREN(Fruit@row), "Apple")

Did it work?

Andrée Starå

Hi Jean Kim,

You have a Parenthesis to many, and you don't need to use Square Brackets.

Square Brackets are only needed when the column name includes a number, space, or a special character.

Try something like this.

=SUMIFS(color99:color104, Fruit99:Fruit104, "Apple")

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jean Kim

ahhh. it works! thanks heaps!

Jean Kim

Bumping up.

Is there a way to achieve the same using "children". Range will always be under the children level.

Instead of specifying row numbers, can we have a formula that covers all rows under children level?

So i dont have to worry about adding new rows.

Andrée Starå

@Jean Kim

Excellent!

Happy to help!

Andrée Starå

@Jean Kim

Yes,

Try something like this.

=SUMIFS(CHILDREN(color99), CHILDREN(Fruit99), "Apple")

or

=SUMIFS(CHILDREN(color@row), CHILDREN(Fruit@row), "Apple")

Did it work?

Jean Kim

Genius!

This works!

=SUMIFS(CHILDREN(color@row), CHILDREN(Fruit@row), "Apple")

The other one didn't work. But I can use above.

Thanks again~~~

Andrée Starå

Excellent!

You're more than welcome!

Jean Kim

Actually This also works.

99 should be the specific parent row number. =D

=SUMIFS(CHILDREN(color99), CHILDREN(Fruit99), "Apple")

Andrée Starå

Yes, both should work.

If possible I prefer the @row because you don't need to think about the row numbers and it takes less processing when something changes.