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.

Find more posts tagged with

Formulas

Project Management

Accepted answers

All comments

Paul Newcome

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)

SUMIFS(CHILDREN([Baseline Labor]@row), CHILDREN([Item Name]@row), @cell <> "Strategic Plan Reserve")

SoS | Dan Palenchar

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

Kirby K

@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

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?

Kirby K

@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.

Kirby K

@Paul Newcome

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?

Paul Newcome

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

Kirby K

@Paul Newcome

Which Column?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.