How can I find which options were NOT selected?

Mike Beam

Hi, I have a column that is a single-select drop-down list of hundreds of location names. Each form user is supposed to submit and select their location name. My goal is to find out which location has not submitted a form. What formula could I use so that I can easily find out which location name was not submitted by anyone?

To simplify this: For example, if I had a list of 10 users and only 7 of the 10 users submitted, what formula could be used to quickly find out which 3 did not submit?

Thanks for any/all guidance.

Paul Newcome

You would simply have a column that contains all of the locations. Then you would have a COUNTIFS on this same sheet that uses cross sheet references to count how many times "Location@row" is present in the {Location Column} of the other sheet.

https://help.smartsheet.com/function/countifs

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

.

Paul Newcome

I would suggest a second sheet that has all locations listed on their own row. Then in the next column use a COUNTIFS to count how many submissions were made for Location@row. If that number is zero, it means nothing has been submitted for it.

Mike Beam

@Paul Newcome Thank you. I think you're right about how to do it. But can you help me put the structure of the formula together too? I'm going wrong here. Not sure how to set it up to look at the other sheet and also tie into the first column on the new sheet. Sorry.

Paul Newcome

You would simply have a column that contains all of the locations. Then you would have a COUNTIFS on this same sheet that uses cross sheet references to count how many times "Location@row" is present in the {Location Column} of the other sheet.

https://help.smartsheet.com/function/countifs

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

.

Mike Beam

Thanks, @Paul Newcome

I was making it too complicated before. Thank you. This is the formula I went with and it works (tells me if I have 0 matches in the original sheet, duplicates, and even how many duplicates).

=COUNTIFS({Sheet 1 Name Range 1}, [All Possible Locations column name on Sheet 2]@row)

Paul Newcome

Glad you got it sorted. Happy to help. 👍️