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

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!

Best Answer

Answers

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

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

  • 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

  • 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"])

  • 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

  • Yes, it worked! Thank you for the response!

  • Wonderful! Happy to help 🙂

Sign In or Register to comment.