Adding total number of drop down options selected

Options

I am having trouble with finding a formula that will count the total number of each multi selection drop down option in a sheet (Error type).

I am tracking error types and need to have a monthly sum of each error type:

Wrong Laterality

Order Type Error (Commercial vs WC)

Misspellings/Cross outs

Sx order missing

Abbreviations

Med Hx/Medications not updated

Missing vendor

Missing duration

Missing facility/location


Not sure how I should go about setting up a column for the values, ultimately these would be used for a dashboard so I dont know if its best to keep it on this sheet or create one and link it.


Any help is greatly appreciated!


Sharon

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Sharon,


    Try this formula:

    =countif([error type]:[error type], HAS(@cell,"Wrong Laterality")

    You could then reuse the formula by replacing "Wrong Laterality" for each error type.

    As for where to put the formula, that depends on what you really want to do with it. You could create a new sheet to summarize it (then you'd need to use a cross-sheet reference and replace [error type]:[error type] with the {range name} you choose), or just create a summary section of rows at the top of your existing sheet. You could then use the data on your dashboard in a Metrics widget or a Chart widget. Alternately, you could use a Sheet Summary, then create a report from the Sheet summary, and display the report on your dashboard.


    Hope this helps!


    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Sharon,


    Try this formula:

    =countif([error type]:[error type], HAS(@cell,"Wrong Laterality")

    You could then reuse the formula by replacing "Wrong Laterality" for each error type.

    As for where to put the formula, that depends on what you really want to do with it. You could create a new sheet to summarize it (then you'd need to use a cross-sheet reference and replace [error type]:[error type] with the {range name} you choose), or just create a summary section of rows at the top of your existing sheet. You could then use the data on your dashboard in a Metrics widget or a Chart widget. Alternately, you could use a Sheet Summary, then create a report from the Sheet summary, and display the report on your dashboard.


    Hope this helps!


    Best,

    Heather

  • Sharon Zengen
    Options

    This worked!! Thank you so much! Wish I asked sooner

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    I only knew the answer because someone asked a similar question a few weeks ago, and someone answered it! I can't claim that as my own knowledge. :) It's definitely something I'll use in the future, though!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!