Summary Sheet Formula with multiple criteria including choices from a multi-select dropdown

Hello - receiving an"unparseable" error for the following sheet summary formula which does a summary looking at 3 different columns - Sub-LOB, ELEMENT and Status. The Sub-LOB and ELEMENT Columns are multi select dropdown columns. And for the Sub-LOB, I want to count if any of the 3 choices are applicable - MCS CR, MCS FR, MCS O&I

=COUNTIFS([Sub-LOB]:[Sub-LOB], CONTAINS("MCS CR", "MCS FR", "MCS O&I", @cell) ELEMENT:ELEMENT, CONTAINS("battlecard", @cell), Status:Status, "complete")

Thank you smartsheet community!

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @jcismesia76271

You're receiving the error because of the multiple criteria within your first CONTAINS function. To adjust your formula I added an OR function, then within that OR I stated each Sub-LOB criteria within its own CONTAINS function.

Try this:

=COUNTIFS([Sub-LOB]:[Sub-LOB], OR(CONTAINS("MCS CR", @cell), CONTAINS("MCS FR", @cell), CONTAINS("MCS O&I", @cell)), ELEMENT:ELEMENT, CONTAINS("battlecard", @cell), Status:Status, "complete")

Here are some Help Articles I used: OR Function / CONTAINS

Let me know if this works for you!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @jcismesia76271

Try this:

=COUNTIFS([Sub-LOB]:[Sub-LOB], OR(CONTAINS("MCS CR", @cell), CONTAINS("MCS FR", @cell), CONTAINS("MCS O&I", @cell)), ELEMENT:ELEMENT, CONTAINS("battlecard", @cell), Status:Status, "complete")

Here are some Help Articles I used: OR Function / CONTAINS

Let me know if this works for you!

Cheers,

Genevieve

jcismesia76271

Genevieve - thank you so much!! This absolutely worked. And thanks for the links to the help articles.

Genevieve P.

Happy to help! So glad to hear it worked for you.

bradleyesmith86431

I have a similar issue, I thought the example formula above would solve it but unfortunately it has not.

I am trying to Count 2 Columns: [Status] and [Current Resource]

Within the [Status], I want to only count if "Started" and "In Progress" these are both drop down options within the column. I also want to countif in the [Current Resource] Column = "Jane Doe"

I thought this function would work and have copied it and inserted my range and criteria and still get #unparseable

=COUNTIFS([Status]:[Status], OR(CONTAINS("Started",@cell), CONTAINS("In Progress")), [Current PS Resource:Current PS Resource, "Jane Doe"])

I am hoping I am just missing something right in front of me! Any guidance?

Thanks for your support

bradleyesmith86431

I apologize, this is the function formula I have tried:

=COUNTIFS([Status]:[Status], OR(CONTAINS("Started",@cell), CONTAINS("In Progress", @cell)), [Current PS Resource:Current PS Resource, "Billy Newman"])

Genevieve P.

Hi @bradleyesmith86431

It looks like your square brackets at the end are not in the correct place, which is why you're receiving the error.

To reference a column, the individual name needs to be [In These] if there are spaces or numbers in the name, so the formula recognizes where the name starts and ends.

Then you can put a row range of the selection within the column as numbers after each column name, [like this]4:[like this]10 - this would look from the 4th row to the 10th row. If you want the whole column referenced, no matter what row, remove out the row number references:

[Column Name]:[Column Name]

See this article for more information on referencing columns (here).

Applying this to your formula, here's the result:

=COUNTIFS([Status]:[Status], OR(CONTAINS("Started",@cell), CONTAINS("In Progress", @cell)), [Current PS Resource]:[Current PS Resource], "Billy Newman")

Let me know if this works!

Genevieve

bradleyesmith86431

Yes, it worked! Thank you for the response!

Genevieve P.

Wonderful! Happy to help 🙂

Sarita Sah

Hi, Is there a way to add year wise drop-down in the "Summary" of a Smartsheet? I want to display last three years 2021,2022 and 2023 in the report of Summary input. But unable to add all these 3 years in 1 filed in drop down. Example, add all these years in the "Renewal CRs" as drop down and calculate the count for each year. Currently its taken only 1 formula for 2023. Can we add 2022 and 2021 in same filed and display their respective count separately in a sheet Summary report?

Genevieve P.

Hi @Sarita Sah

Yes, you can add multiple formulas together in one Summary Field, or add the text content of the results without summing the data together if that's what you want.

To do so, you'll use the + symbol between each of your formulas and add a text symbol in between as well.

For example:

="2023 Data: " + COUNTIFS(formula for 2023) + "2022 Data: " + COUNTIFS(formula for 2022) + "2021 Data: " + COUNTIFS(formula for 2021)

Does that make sense?

Cheers,

Genevieve

Sarita Sah

@Genevieve P., thanks for looking into it. I have not used and don't know about the formula you suggested. Could you please help correct it, I am getting invalid operations with below.

="2023 Data: " + COUNTIFS(COUNTIFS(Category:Category, "Renewal", [Go Live Year]:[Go Live Year], "2023")) + "2022 Data: " + COUNTIFS(COUNTIFS(Category:Category, "Renewal", [Go Live Year]:[Go Live Year], "2022")) + "2021 Data: " + COUNTIFS(COUNTIFS(Category:Category, "Renewal", [Go Live Year]:[Go Live Year], "2021"))

Genevieve P.

Hi @Sarita Sah

You're very close! I see you doubled the COUNTIFS though, which is not needed. I just meant that you woulda dd the different data for the different years within COUNTIFS(formula) not repeat the formula.

Try this:

="2023 Data: " + COUNTIFS(Category:Category, "Renewal", [Go Live Year]:[Go Live Year], "2023") + "2022 Data: " + COUNTIFS(Category:Category, "Renewal", [Go Live Year]:[Go Live Year], "2022") + "2021 Data: " + COUNTIFS(Category:Category, "Renewal", [Go Live Year]:[Go Live Year], "2021")

Cheers,

Genevieve

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.