Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
count how many times someone is assigned
Answers
-
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.
-
@MaxNaprix Try something like this...
=COUNTIFS([Assigned To]:[Assigned To], HAS(@cell, "John Doe"))
-
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.
-
@Paul Newcome, the formula fails when there are multiple assignees. Please do you know an alternative for a COUNTIF function?
-
@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"?
-
@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 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.
-
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?
-
....{OKR 1 : EBITDA Range 2}, $[Primary Column]@row) covers the multi select (status) column
-
@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.
-
@Paul Newcome Thank you so much! It worked!! I appreciate your help.
Help Article Resources
Categories
Check out the Formula Handbook template!