Exclude Current Row

Kirby K · 02/14/24

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.

Paul Newcome · 02/14/24

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")

SoS | Dan Palenchar · 02/14/24

You could use SUMIFS and exclude some unique parameter associated with that row.

Kirby K · 02/14/24

@Paul Newcome

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))

Paul Newcome · 02/15/24

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?