count how many times someone is assigned

System

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

MaxNaprix

Please Help!

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.

Paul Newcome

@MaxNaprix Try something like this...

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

MaxNaprix

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.

Okey

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

Paul Newcome

@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"?

Okey

@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

Paul Newcome

@Okey Which range is the multi-select column?

Okey

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

Screenshot 2023-04-21 114527sss.png

Paul Newcome

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?

Okey

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

Paul Newcome

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

Okey

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

Paul Newcome

@Okey Happy to help. 👍️