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

jcismesia76271
jcismesia76271 ✭✭
in Formulas and Functions

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!

· Share on FacebookShare on Twitter

Best Answer

Answers

  • jcismesia76271
    jcismesia76271 ✭✭

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

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

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

    · Share on FacebookShare on Twitter
  • bradleyesmith86431
    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

    · Share on FacebookShare on Twitter
  • bradleyesmith86431
    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"])

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    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

    · Share on FacebookShare on Twitter
  • bradleyesmith86431
    bradleyesmith86431 ✭✭✭✭

    Yes, it worked! Thank you for the response!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! Happy to help 🙂

    · Share on FacebookShare on Twitter
  • Sarita Sah
    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?

    image.png


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    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

    · Share on FacebookShare on Twitter
  • Sarita Sah
    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"))

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!