I need to report on a field that has a drop down with multiple selections

Options

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

  • NocturnalAnimal
    Options

    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))

  • mleal
    Options

    Thanks!

    Do you have a sample that I can modify?

  • mleal
    Options

    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.

  • mleal
    Options

    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?

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!