Sum(children()) + sumifs (rows underneath, criteria 1, rows underneath, criteria 2)

Meredith Luschen

I have a budget divided by department and subdepartment. the parent rows rollup the children rows to tell me the grand total for each subdepartment budget. I'm then creating a dynamic view of each subdepartment budget and giving the heads of those subdepartments the opportunity to add new rows (via dynamic view). I would like the Total cell to include the children PLUS any additional rows that meet a certain criteria (ie: department code AND level 0).

thanks!

Paul Newcome

Would your SUMIFS be looking at child rows only?

Meredith Luschen

I'm hoping to Sum(children())+sumifs(total:total, Department:Department, "biostats", Level:Level, "0") Where "Level:Level" is a column counting the ancestors.

To answer your question - No - the sumifs part of the formula will be summing non-children rows.

Part of the issue is I'm trying to create this "total" cell in the same column that I'm trying to sum. This would normally create a circular reference but I'm wondering if there is a way to ask the formula to either: 1. only look at the rows BELOW or 2. have the formula operate on some kind of order of operations....first match department, then match ancestors, then add it up.

Meredith Luschen

I know I could create a "helper column" to add up all the lines that have "biostats" as the department but I was hoping to avoid another helper column. and I was curious if you could combine sum(children()) with a sumifs

Paul Newcome

You can combine the SUM/CHILDREN with the SMIFS, but you are going to have to move it out of the column being evaluated to avoid the circular reference error. There is currently no way to dynamically reference a specific row number such as

[Column Name]@row:[Column Name]#

and have the # automatically replaced with the last row number as new rows are added.

Moving the formula out of the range being evaluated, you can insert a column to duplicate the row number and then adjust your formula to only evaluate row numbers that are greater than the current row, but that would still require evaluating the entire column which will again produce the circular reference error.