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!

Answers

  • Don C
    Don C Employee

    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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!