Adding Positive Parents

Hello all,

I am trying to set a formula up that adds the sum of several Parent lines, but only if they are positive. Adding all parents under one banner i use the Sum(Children) however, I tried to add only positive parents by using a SumIf(Cell1:Cell5, >0, Cell1:Cell5) but in doing so only managed to add all positive numbers underneath the parent cell as well (which amounts to about a million dollar difference so little bit of an issue). Does anyone know how I can set up, or even an exact formula that I can modify to make it only add up the positive parent cell amounts?

Find more posts tagged with

Manufacturing

Finance

Formulas

Solution Center

Project Management

Comments

Andrée Starå

Hi Mike,

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

MikeCorvin

Andree,

The numbers in green are the numbers I want to add, I have expanded one of the parents so you can see the children underneath. All rows with Blue on either side are parent rows whereas in the expanded child you can see white and gray to either side.

Mike

Andrée Starå

Try something like this.

I've added a checkbox helper column where the cells are checked if it's a parent.

=SUMIFS(Amount:Amount; Amount:Amount; >0; Helper:Helper; 1)

The same version but with the below changes for your and others convenience.

=SUMIFS(Amount:Amount, Amount:Amount, >0, Helper:Helper, 1)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Best,

Andrée

MikeCorvin

Andree,

What do you mean by a checkbox helper?

Mike

Andrée Starå

Sorry, I forgot to add the following.

The Checkbox helper column is a checkbox type column that we're using to check if it's a parent. The box will get checked if it's a parent.

Try something like this.

=IF(COUNT(CHILDREN()) > 0; 1; 0)

The same version but with the below changes for your and others convenience.

=IF(COUNT(CHILDREN()) > 0, 1, 0)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Best,

Andrée

MikeCorvin

Thank you Andree it sure did!

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…