# Count/sum only if one of children is a specific value and scope

Options
✭✭✭✭✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

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

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Hi Andree,

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?

• ✭✭✭✭✭✭
Options

I was wondering if you can help me on this?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!