count how many times someone is assigned

Options
Employee
This discussion was created from comments split from: How to count how many users assigned to in a sheet.

• Options

I need to count how many times was the person (referenced from the cell) was assigned to the assigned to column, taking into consideration that there are tasks with multiple assignees as well as just with the single one.

I have been trying COUNTIF and FIND and some other options but it doesn't work.

• ✭✭✭✭✭✭
Options

@MaxNaprix Try something like this...

=COUNTIFS([Assigned To]:[Assigned To], HAS(@cell, "John Doe"))

• Options

OMG Thank you, looks like it works! The only thing is that for some reason it counts it correctly but adding +1. I don't know why but I just added -1 to the formula.

• ✭✭✭✭
Options

@Paul Newcome, the formula fails when there are multiple assignees. Please do you know an alternative for a COUNTIF function?

• ✭✭✭✭✭✭
Options

@Okey The formula with the HAS function is exactly what you would need for multiple selections in a cell. Can you provide more details as to what you mean by "Fails"?

• ✭✭✭✭
Options

@Paul Newcome I use a COUNTIF formula =COUNTIFS({OKR 1 : EBITDA Range 1}, HAS(@cell, [Column4]\$3), {OKR 1 : EBITDA Range 2}, \$[Primary Column]@row) but with two assignees in the referenced row, it returns a "0". If there is only one name in the assigned column, then it returns the accurate number

• ✭✭✭✭✭✭
Options

@Okey Which range is the multi-select column?

• ✭✭✭✭
Options

@Paul Newcome The formula is references another sheet. It captures the whole column (Owner:Owner). The formula is meant to count the statuses for each or multiple assignees of a task.

• ✭✭✭✭✭✭
Options

I understand that it references another sheet and covers an entire column, but which range in the formula is the one that covers the multi-select?

• ✭✭✭✭
Options

....{OKR 1 : EBITDA Range 2}, \$[Primary Column]@row) covers the multi select (status) column

• ✭✭✭✭✭✭
Options

@Okey You need to use the HAS function on the multi-select criteria, so your most recent comment indicating which range/criteria set covers the multi-select would be the criteria to use the HAS on.

• ✭✭✭✭
Options

@Paul Newcome Thank you so much! It worked!! I appreciate your help.

• ✭✭✭✭✭✭
Options

@Okey Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!