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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!