Trying to get a count of status for specific team members

Alisa Buck

I am trying to find out how many initiatives with a specific status have certain team members in the Project Team column. For example, how many initiatives is Leo on the team where the status is Not Started? I have tried the following:

=COUNTIFS({EITPort_Status_24}, "Not Started", FIND("Leo Liao", @cell, >0)) - Gives me an Invalid Operation error

=COUNTIFS({EITPort_Status_24}, "Not Started", HAS(@cell, "Leo Liao")) - Gives me an Incorrect Argument error

Any thoughts on how to make this work?

Mark.poole

@Alisa Buck How large is the reference you are using is it covering all the columns required?

Also. COUNTIFS() function works like this.

=COUNTIFS(Range,Criteria, Range, Criteria,Range, Criteria)

you have to give a range to look before every criteria to pull the information.

Paul Newcome

You are close with both.

=COUNTIFS({EITPort_Status_24}, "Not Started", {Source Sheet Contact Column}, FIND("Leo Liao", @cell) >0)

=COUNTIFS({EITPort_Status_24}, "Not Started", {Source Sheet Contact Column}, HAS(@cell, "Leo Liao"))

Alisa Buck

Thanks all! I got it to work using…

=COUNTIFS({EITPort_Team_24}, FIND("Leo Liao", @cell) > 0, {EITPort_Status_24}, FIND("Not Started", @cell) > 0)