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
-
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
Answers
-
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
-
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"])
-
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 🙂
-
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?
-
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
-
@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"))
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!
