I need to report on a field that has a drop down with multiple selections
I'd like to prepare a chart that would tell me how many times a specific entry occurred. is that possible or do I need to do this in Excel? I'd like to add this information to a dashboard.
Answers
-
First you would need to have a formula to count the data that you would want. then you can make a chart in a dashboard. I usually create a form of a metrics page with the data that I want to include on the Dashboard. As to the specific formula that you would use, a bit more information would be needed.
If you just want to count the amount of answers then =COUNTM(Search_range1)
If you would like to search in the cell for a specific entry =COUNTIF(range, CONTAINS("xxx", @cell))
-
Thanks!
Do you have a sample that I can modify?
-
Thank you. I think I got it, there's just a lot of options, so I have to create one for each, thought maybe there was an automated way that would filter based on the multientry and provide a summary.
-
What would the formula be if I wanted to list
=COUNTIFS({PRC 11-3-21 - 11-4-21 Rough Objection List Range 3},CONTAINS("Mr. Brennan", @cell),[PRC 11-3-21 - 11-4-21 Rough Objection List Range 1},CONTAINS("a lot", @cell)]
So I'd like to get the number of times Brennan appears on the first range and the term a lot in the second range.
Does that make sense?
-
Hi @mleal
You're close with the last formula! However I see you have a square bracket around your cross-sheet reference; this will need to be a {curly} one for a cross-sheet range.
Here's the structure of a cross-sheet COUNTIFS:
=COUNTIFS({First Column}, "First Criteria", {Second Column}, "Second Criteria")
However, if you have a multi-select column you will want to use HAS, and if you have a text cellw ith multiple options then you will want to use CONTAINS, as mentioned earlier.
I would suggest something like this:
=COUNTIFS({PRC 11-3-21 - 11-4-21 Rough Objection List Range 3}, CONTAINS("Mr. Brennan", @cell),{PRC 11-3-21 - 11-4-21 Rough Objection List Range 1}, CONTAINS("a lot", @cell))
Another way to make this a little easier on yourself is to list all the names down one column on the left. Then you can have your formula reference the cell to the left instead of writing in the name "Mr. Brennan" into the formula and needing to adjust this for each line.
=COUNTIFS({PRC 11-3-21 - 11-4-21 Rough Objection List Range 3}, CONTAINS([Person Column]@row, @cell),{PRC 11-3-21 - 11-4-21 Rough Objection List Range 1}, CONTAINS("a lot", @cell))
Let me know if this makes sense or if I can clarify anything further for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!