Extract data on drop-down list

Hermien Venter

I have a drop-down list with names, and am trying to get a count of items (rows) against each name in the drop-down list. That would be as simple as =countifs, HOWEVER, I'm stuck in that that there can be more than one name in the cell of a specific row, and in this case the formula doesn't recognize any individual name in that "group" of names in that specific row. Any advice on how to solve that? Thanks!

Dale Murphy

@Hermien Venter This from @Mark Cronk

There are 2 unique functions in Smartsheet to deal with multiple select fields; HAS and COUNTM.

COUNTM: Counts the number of elements in a multi-contact or multi-select dropdown column cell or cell range. Returns the total number of elements found. https://help.smartsheet.com/function/countm

HAS: Search for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found.https://help.smartsheet.com/function/has

Check those out.

• countif(HAS()) is probably what you need ...

dm

Paul Newcome

In that case you will need to work in a FIND function.

=COUNTIFS({Range}, FIND("John Doe", @cell) > 0)

Dale Murphy

@Hermien Venter This from @Mark Cronk

There are 2 unique functions in Smartsheet to deal with multiple select fields; HAS and COUNTM.

COUNTM: Counts the number of elements in a multi-contact or multi-select dropdown column cell or cell range. Returns the total number of elements found. https://help.smartsheet.com/function/countm

HAS: Search for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found.https://help.smartsheet.com/function/has

Check those out.

• countif(HAS()) is probably what you need ...

dm

Paul Newcome

Are you using a contact type column?

Mark Cronk

Thanks @DMurphy , Glad you found value in one of my previous posts. Mark

Hermien Venter

Paul, yes, it's a contact type column

Paul Newcome

In that case you will need to work in a FIND function.

=COUNTIFS({Range}, FIND("John Doe", @cell) > 0)

Hermien Venter

DMurphy, I found your fomula doesn't work if there are at times multiple names in a cell, however, Paul Newcome's solution overcomes that.

Thanks a ton, I appreciate this!