Trying to summarize how many cells are NOT something else (an "other" category)


Hi All - I have over 10 options for a column with drop-downs. Most occur more than many times, but there are about 5 or 6 options in the drop-down menu that only occur once or twice. In my summary sheet, I want to lump all those drop-down selections as "Other." But I need a way to tally them. I'm using this to tally the 9 categories and basically thought I would use a NOT formula to exclude each of those 9. Here's what I have:

=COUNTIFS({NAME OF SHEET Range 3}, HAS(@cell, "Technological / IT"))

I'm using that to tally each cell that includes the drop-down selection of Technological / IT and so on and so forth for each of the categories. But what formula would I use to capture EVERYTHING BUT those 9 I'm already counting? How do I do that?

I tried this and it didn't work:

=COUNTIFS({NAME OF SHEET Range 3}, NOT(@cell, "Technological / IT"), NOT(@cell, "Health-Related Emergency"), NOT(@cell, "Fire"), NOT(@cell, "Election"), NOT(@cell, "Severe Weather"), NOT(@cell, "Active Shooter"), NOT(@cell, "Utilities Outage"), NOT(@cell, "All Hazards"), NOT(@cell, "Injuries/Mass Casualties"))

It gave me an incomplete argument set error.

Any thoughts?


  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

    I'm thinking that you're using the HAS function in that first formula because you've got a multi-select drop-down, so not sure if what I'm putting in here will work for your specific scenario, but maybe it'll get you going in the right direction.

    =COUNTIFS(testing:testing, NOT(OR(@cell = "Option 1", @cell = "Option 2", @cell = "Option 3", @cell = "Option 4")), testing:testing, NOT(ISBLANK(@cell)))

    Replace what I used 'testing' as a column header with your {NAME OF SHEET Range 3}.

    Replace what I used as 'Option 1, Option 2, etc.' with your specific items.

    See if the combination of NOT and OR help get you what you're looking for. The second range/criteria was needed to not count the blank rows at the bottom of the sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!