SUMIFS with 9 criteria
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("1255Grassroot........ 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?
Answers

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]))
Hope this helps and that you have a great day,
Jason
Help Article Resources
Categories
Check out the Formula Handbook template!