How do I change the formula in the Metadata sheet so that I can include data from two columns?
Hi Smartsheet Community
I am trying to edit a donut chart that displays projects by Change Impact (HighMediumLow) across all stages (Initiate, Plan, Implement, Close, Closed)  while excluding Closed projects.
Currently my data is taken from the metadata sheet  which unfortunately doesn't include the distinction by stage  so I can't edit the formula in that sheet. However I have a Summary Sheet which includes all the information that I need.
Q: How do I change the formula in the Metadata sheet so that I can include data from two columns (Change Impact and Stage, source Summary Sheet) without creating another Summary Sheet or Report?
Thanks for your help
Rossella
Best Answer

Hi Genevieve
I have done it! I forgot to reference the right Column from the Summary Sheet. All good now.
COUNTIFS({ChangeImpact}, "Red", {CurrentStage}, <>"Closed")
COUNTIFS({ChangeImpact}, "Yellow", {CurrentStage}, <>"Closed")
COUNTIFS({ChangeImpact}, "Green", {CurrentStage}, <>"Closed")
Thanks!
Rossella
Answers

Hi all
here is a screenshot of my Summary Sheet. I would like a formula to calculate the number of projects with Red (high)  Yellow (medium)  Green (low) impact  across all active projects (ie excluding the Closed ones, and blank cells).
I have checked a post from Paul N and applied the formula below but I get an UNPARSEABLE / INVALID REFERENCE message.
=COUNTIFS({Change Impact}, "High", {Current Stage}, AND(NOT(isblank@cell)), NOT(CONTAINS("Closed"),@cell))
The orginal working formula is below  but I need to be able to exclude the 'Closed' initiatives from it.
=COUNT(COLLECT({ChangeImpact}, {ChangeImpact}, @cell = "Red"))
How can I edit the formula so it gives me what I need?
Thanks a lot
Rossella

Hi @Rossella
Try something like this:
=COUNTIFS({Change Impact}, "Red", {Current Stage}, <> "Closed")
The <> symbol means "does not equal" so this will count all the rows that have a Red status ball that do not have "Closed". You can title the field "High", but the formula won't know that Red = High which is why you're searching for "Red" instead.
If you want to narrow down your count to specific Stages (e.g. "Pipeline"), then instead of excluding Closed you would search only for "Pipeline", like so:
=COUNTIFS({Change Impact}, "Red", {Current Stage}, "Pipeline")
And if you're looking for different colours, you just need to swap out what you're looking for in the Change Impact column:
=COUNTIFS({Change Impact}, "Yellow", {Current Stage}, <> "Closed")
See: COUNTIFS Function
Keep in mind that {these references} are crosssheet references looking into your sheet. Here's the documentation on how to create a crosssheet reference: Create cross sheet references to work with data in another sheet
If you're building out the formula in the same sheet, such as in a Sheet Summary field, then in this instance you'd want to use an insheet column reference instead:
=COUNTIFS([Change Impact]:[Change Impact], "Red", [Current Stage]:[Current Stage], <> "Closed")
See: Create a Cell or Column Reference in a Formula
Cheers,
Genevieve

Thank you Genevieve
I get the 'invalid reference' error when applying the formula below:
COUNTIFS({Change Impact}, "Red", {Current Stage}, <> "Closed")
I wonder, is it possible to exclude the Closed initiatives from the 'COUNT formula'?
Currently my formula references to the Summary Sheet and it sits in the Metadata sheet:
COUNT(COLLECT({ChangeImpact}, {ChangeImpact}, @cell = "Red"))
I have tried the following but I get the 'unparseable' error:
COUNT(COLLECT({ChangeImpact}, {ChangeImpact}, @cell = "Red") ({Current Stage}, {Current Stage}, <>"Closed"))
Thanks for your help
Rossella

Hi Genevieve
I have done it! I forgot to reference the right Column from the Summary Sheet. All good now.
COUNTIFS({ChangeImpact}, "Red", {CurrentStage}, <>"Closed")
COUNTIFS({ChangeImpact}, "Yellow", {CurrentStage}, <>"Closed")
COUNTIFS({ChangeImpact}, "Green", {CurrentStage}, <>"Closed")
Thanks!
Rossella

Hi @Rossella
Wonderful!!! I'm glad you were able to get these formulas working, welldone. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!