Formula Query - Nested IF Statement

Hi, i have a problem where i am tendering on projects, where i request quotes, input the prices, then i will adpot the price i wish to use that makes up the total. Im struggling to get the formula right. Basically it should be the Quantity x Rate, unless it has children and the "star icon" is checked, in which case it will be (sum of children)

See below screen shot. The problem is when there is no children (level 5) i still want it to calculate the Quantity x Rate at level 4 as i dont always have Level 5 (only when quoting) and this seems to be the issue.

Thankyou,

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Instead of specifying what level it is on, just specify the difference between parent and non-parent rows.

=IF(COUNT(CHILDREN(Level@row)) = 0, Quantity@row * Rate@row, insert parent row piece here)

Paul Newcome

Ok. So in the instance that a row does have children, you want to sum the children that have [Price Used] checked. In all other instances, you want to multiply quantity by rate.

If that is correct, we will need a helper column (can be hidden after setting up) called "Price" that is simply

=Quantity@row * Rate@row

This will be applied as a column formula.

Then in the column where you want the final amount:

=IF(COUNTIFS(CHILDREN([Price Used]@row), @cell = 1) > 0, SUMIFS(CHILDREN(Price@row), CHILDREN([Price Used]@row), @cell = 1), Price@row)

All comments

Paul Newcome

Instead of specifying what level it is on, just specify the difference between parent and non-parent rows.

=IF(COUNT(CHILDREN(Level@row)) = 0, Quantity@row * Rate@row, insert parent row piece here)

Boyd

Hi @Paul Newcome
Thanks for the help with the count function.
I have one more issue. The heirarchy i am looking for is this;
If it has children and the "price used" column is checked, then i want to sum the children values.
If it has children and the "price used" column is not checked, then i want to (sum quantity * rate @ row)
If it has not children, just (sum quantity * rate @ row)
Currently the formula is working for the rows with no children, but if it does have children and "price used" is not checked, it is coming up as $0 amount.
Basically i want to input values in the parent column as place holders until the price is submitted, but then i would have to overide the column formula to do this.
Hope that makes sense.
Thanks in advance.

Paul Newcome

Would [Price Used] be checked on the parent row or on the child rows?

Boyd

@Paul Newcome It would be used in the child rows.
Parent row is the task that needs to be completed, children rows are the companies that we obtain quotes from.
Once the quotes come in, we evalutate them, and adopt one of them, or multiple, to form our pricing.
Under any given parent row there could be 1-5 items that are checked that i want to sum up to the parent row.

Boyd

Whilst we are waiting for pricing, we will often have an estimate within the parent row that we want to override once prices come in and checkbox is marked.

Paul Newcome

Ok. So in the instance that a row does have children, you want to sum the children that have [Price Used] checked. In all other instances, you want to multiply quantity by rate.

If that is correct, we will need a helper column (can be hidden after setting up) called "Price" that is simply

=Quantity@row * Rate@row

This will be applied as a column formula.

Then in the column where you want the final amount:

=IF(COUNTIFS(CHILDREN([Price Used]@row), @cell = 1) > 0, SUMIFS(CHILDREN(Price@row), CHILDREN([Price Used]@row), @cell = 1), Price@row)

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.