SUMIFS with 9 criteria

Mel_3

I am trying to sum specific values based on a dropdown list in one column and display that sum in another column. I have an "Amount" column , a "Total Final OEP 0708 Cost Center Spent" column , and a "Cost Center" dropdown column. I want to sum CHILD rows in column "Amount" based on the drop down option that is selected. Right now I have four drop down choices and I want to sum those that start with "0708" (see screenshots attached) I used this formula to sum for those three criteria and it works fine:

=SUM(SUMIFS(CHILDREN(Amount@row), CHILDREN([Cost Center]@row), CONTAINS("0708- Traditional", @cell)), SUMIFS(CHILDREN(Amount@row), CHILDREN([Cost Center]@row), CONTAINS("0708- Multicultural", @cell)), SUMIFS(CHILDREN(Amount@row), CHILDREN([Cost Center]@row), CONTAINS("0708- WISF", @cell)))

The issue is I was told that there are 6 more options that need to be added to the drop down that fall under the "0708" category , meaning I would need to SUM 6 more values than just those three^. Is there a better way to do this other than adding 6 more SUMIFS functions to the formula above?

Also, I used SUMIF(CHILDEREN((Amount@row), CHILDREN([Cost Center]@row),NOT(CONTAINS("1255-Grassroot........ but I was also told that the number of drop down options that are NOT of type "0708" are over 5 .... so that would leave me adding just as many functions within the SUM ....back to square one.

How to I do this without creating a formula with 6+ SUMIFS functions?

Capture.PNG

Capture1.PNG

Jason Albrecht

Hi @Mel_3

I don't normally work with Parent and Child rows, but I triled the following and it seemed to work:

=SUMIF(CHILDREN([Cost Center]:[Cost Center]), CONTAINS("0708", @cell), CHILDREN([Amount]:[Amount]))