Exclude Current Row
I am working on a formula where my final piece needs to exclude the row my formula is in.
My Current formula is:
=MAX(0, (PARENT([FCB LAB]@row)  SUM(CHILDREN([Baseline Labor]@row))) / 45)
Can someone show me how to exclude the row my formula is in when summing the children of the Baseline Labor?
For further clarification: I want to exclude $9,181 out of the SUM(CHILDREN([Baseline Labor]@row)) Formula above making the Strategic Planning Parent Row = 5,260.41
NOTE: I would like for this formula to be replicated in other sheets so I want to make sure I am not using specific cell values.
Answers

There are a number of ways to do this. The two most straightforward would be to insert a checkbox type column and check the boxes on rows that you want included (or check boxes on the row(s) you want excluded) and then work this into your formula using a SUMIFS instead of a SUM, or you can use a SUMIFS to enter what text is in the row you want excluded.
SUMIFS(CHILDREN([Baseline Labor]@row), CHILDREN([Checkbox Column]), @cell = 1)
or
SUMIFS(CHILDREN([Baseline Labor]@row), CHILDREN([Item Name]@row), @cell <> "Strategic Plan Reserve")

You could use SUMIFS and exclude some unique parameter associated with that row.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

I tired using your suggestion and this is the full formula but the "Strategic Plan Reserve" Row was not excluded from the final Baseline Total.. What am I missing here?
=MAX(0, (PARENT([FCB LAB]@row)  SUMIFS(CHILDREN([Baseline Labor]@row), CHILDREN([Item Name]@row), @cell <> "Strategic Plan Reserve")) / 45))

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it? Can you also provide a screenshot of the "Strategic Plan Reserve" cell also open as if you are about to edit that?

@Paul Newcome, Sure!
The formula in question is in the Est. Hours Column on the Strategic Plan Reserve Row.
The Strategic Plan Reserve Cell Does not have any formulas attached.
In Addition, I moved the FCB LAB Column over just for a better visual.

After looking at it further.. The true number we need in the Baseline Labor Column is the the Parent total of the Task Row.
=MAX(0, (PARENT([FCB LAB]@row)  SUMIFS(CHILDREN([Baseline Labor]@row), CHILDREN([Item Name]@row), @cell <> "Task")) / 45))
But this formula is showing Unparseable.. Am I missing something simple?

The SUMIFS I provided goes in the top row where you have "Strategic Planning".

Which Column?
Help Article Resources
Categories
Check out the Formula Handbook template!