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

Michaela Kamenska

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?

Andrée Starå

Hi @Michaela Kamenska

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!

Michaela Kamenska

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.

Andrée Starå

@Michaela Kamenska

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)

Michaela Kamenska

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?

Michaela Kamenska

Hi @Andrée Starå,

I was wondering if you can help me on this?