SUMIFS Based on Children

Hello,

I use Smartsheet as a future revenue reporting tool.

I have a total at the top for individual managers, formula for total is a SUMIF function.

Formula is =SUMIF(CHILDREN(WAM$8), WAM1, CHILDREN([Est Revenue]$8))

WAM 8 is our total for everyone section, WAM1 is for individual managers, Est Revenue 8 is our total Est Revenue.

We use a Confidence Level row in our database as well, delineating work that is either +80%, or Below 80.

I am trying to use a SUMIFS Function underneath the total for each of our managers, so we can see instantly what work is +80%, or below 80. My end goal is to have 2 items underneath each manager at the top, showing +80% value and Below 80 Values.

Thanks!

Find more posts tagged with

Accepted answers

All comments

Don C

Hi @Joshua York ,

With a SUMIFS function, you'll want to make sure the [sum_range] appears in the first part of the formula. This is so you can add additional criteria to the end of the formula. In contrast, the [sum_range] appears in the last part of a SUMIF function.

The syntax for SUMIFS should look like the following:

=SUMIFS([sum_range], criterion_range1, criterion1, criterion_range2, criterion2, ....)

Try something like this:

If you have a "Confidence Level" column in your sheet to assign the percentage for each row, then it could look like the following:

For Above 80%

=SUMIFS(CHILDREN([Est Revenue]$8), CHILDREN(WAM$8), WAM1, CHILDREN([Confidence Level]$8), > 0.8)

For Less than or Equal to 80%

=SUMIFS(CHILDREN([Est Revenue]$8), CHILDREN(WAM$8), WAM1, CHILDREN([Confidence Level]$8), <= 0.8)

Let me know if this makes sense or if you need any additional help! If you need more help, it would be useful to know what you tried (copy/paste the formula) and to see any screen captures of the source sheet (but please block out any sensitive data).

Cheers,

Don

Tabbi

I'm curious if this formula could be used to add children on rows with a check box column that is checked?

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.