Count/sum only if one of children is a specific value and scope
Hello everyone,
I was wondering if someone can help - I would like to do some financial departmental metrics in terms of revenue. Now, I have a sheet with about 300 different projects and would like to create another sheet where I will only see the sum of the revenue/totals for each of the 3 columns (as below). My costs are always in the parent row, however, not all parent rows have children with scope. As shown below, I'd only like to count the costs if the child row contains a certain value (e.g. Jane Green) and scope. Is there a formula for this I could use?
Answers
-
You can't use the CHILDREN function in a Cross-sheet formula, so you'd need to add one or multiple helper columns to make it work.
If everything is in one sheet, I would recommend doing the sheet calculation in a specific section in the sheet or the Sheet Summary and then cell-link that to another sheet if needed.
Make sense?
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Andrée Starå ,
Thank you for your response.
I know what you mean, but I wondered if you would be able to help me with the formula I'd put in the summary (or my helper row that I already have) so that it counts values/costs in the parent row if one of the children is as outlined above and date would contain year 2020? I'm not very good with more complex formulas, I'd presume we'd use SUMIFS but that's where I kinda lose it.
-
Happy to help!
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andree,
Thank you for your help.
I published the sheet with the column names as I have in the original sheet (deleted irrelevant columns) and replaced information. These columns should suffice.
https://app.smartsheet.com/b/publish?EQBCT=4a0648783339490ba3795a94e79564f1
Will this be sufficient or do I need to share the sheet with you directly?
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!