Counting multi select column that is also contact list

Sborror

I have Contact list - multiple resources can be assigned task

I want to count the number of times a particular resource has been assigned a task.

I've tried these both:

=COUNTIF({Staff Actions Range 4}, HAS("Joe X", {Staff Actions Range 4}))

=COUNTIF({Staff Actions Range 4}, CONTAINS("Joe X", {Staff Actions Range 4}))

Both return 0 which is not correct

Paul Newcome

@Sborror Please don't forget to mark the response that contains the correct solution as "helpful".

Paul Newcome

HAS looks for an exact match, so it will not count any cells that have additional people listed.

CONTAINS just doesn't like contact type columns.

I suggest a FIND function instead. The FIND function will search the cell for your specified text and will produce a number based on where within the string your text is found. That means a 0 (zero) implies the text is not found, and any number greater than zero means that the text is found somewhere.

=COUNTIF({Staff Actions Range 4}, FIND("Joe X", @cell) > 0)

Sborror

Paul apologies for asking an ignorant question but the @cell references what exactly? In my formula I'm referencing a different sheet --

countif({Staff Actions Range 4}, FIND("Joe X", {Staff Actions Range 4} > 0))

This returns invalid action....

Paul Newcome

The @cell reference basically tells the formula to look across the previously established range and evaluate on a cell by cell basis. There is also a misplaced parenthesis in your formula. You need to close off the FIND function before the greater than zero portion. As long as your cross sheet reference was created using the appropriate steps, you should be able to use the formula I provided exactly as it is.

Sborror

OMG!! Thank you!!😀

Paul Newcome

Happy to help!👍️

Paul Newcome

@Sborror Please don't forget to mark the response that contains the correct solution as "helpful".