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 (High-Medium-Low) 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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

Rossella

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

Genevieve P.

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 cross-sheet references looking into your sheet. Here's the documentation on how to create a cross-sheet 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 in-sheet 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

Rossella

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

Genevieve P.

Hi @Rossella

Wonderful!!! I'm glad you were able to get these formulas working, well-done. 🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.