Counting multi select column that is also contact list
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
Best Answer

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

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)

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....

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.

OMG!! Thank you!!😀


@Sborror Please don't forget to mark the response that contains the correct solution as "helpful".
Help Article Resources
Categories
Check out the Formula Handbook template!