SUM IF with OR and CHILDREN

alexjohnson

Hello,

I am trying to add one additional condition to this statement below, but can't seem to figure it out.

Here's what I have currently (which works):

=IF($[Row Identifier - Budget]@row = "Parent", SUMIF(CHILDREN([Alt Cost Center]@row), "", CHILDREN()), "")

And I'd like to add an additional option that says if [Alt FY]@row has data in it, to also follow the same protocol. They both do not need to be true, just one or the other.

Here's my work in progress...any help is much appreciated!

=IF($[Row Identifier - Budget]@row = "Parent", SUMIFS(CHILDREN([Alt Cost Center]@row, "", OR[Alt FY]@row)), "", CHILDREN())

SJ Sellers

It isn't possible to do what you are trying to do. OR can only be used in SUMIFS or COUNTIFS to check for two conditions in the same column.

What you want to do is create two different SUMIFS statements, and then add them together.

=IF($[Row Identifier - Budget]@row = "Parent",FIRST_SUMIF_STATEMENT + SECOND_SUMIF_STATEMENT)

alexjohnson

Ah! Thank you @SJ . Do I need to repeat CHILDREN()), "") after each SUMIF or only at the end of the added SUMIFs?

alexjohnson

This works! But it doubles my budget column amount. How do I get these to operate independently from one another?

=IF($[Row Identifier - Budget]@row = "Parent", SUMIF(CHILDREN([Alt FY]@row), "", CHILDREN())) + SUMIF(CHILDREN([Alt Cost Center]@row), "", CHILDREN())

SJ Sellers

It might help to have a screenshot of your data to suggest the best approach.

If your budget is being doubled, I'm guessing that you have rows that meet both conditions - is that right? If that is the case, you would want to then SUBTRACT the budget amounts where both conditions are true using a SUMIFS.

Like this:

=IF($[Row Identifier - Budget]@row = "Parent", SUMIF(CHILDREN([Alt FY]@row), "", CHILDREN())) + SUMIF(CHILDREN([Alt Cost Center]@row), "", CHILDREN()) - SUMIFS(CHILDREN(), CHILDREN([Alt Cost Center]@row), "", CHILDREN([Alt FY]@row), "")