Functions in a contact column

Lynn Meadow

I have several columns of data with contacts. The first column is if a person is the "lead" on a project, and the other 3 columns refer to 3 different roles (CPE, ID, MM). A person could be included in teh lead column, and then also identified in one of the 3 columns. I'm trying to figure out the number of projects they are assigned to without duplicating the numbers when they are the lead and also one of the 3 roles.

It also has to be for just the active projects.

The first two parts i have working.I calculate the number of "In Progress projects that someone is the lead on with the following formula:

=COUNTIFS({Project Lead}, [Team Member]@row, {Project Status}, CONTAINS("In Progress", @cell))

Then i calculate the number of projects where they are a team member:

=COUNTIFS({ID Team}, FIND([Team Member]@row, @cell) > 0, {Project Status}, CONTAINS("In Progress", @cell))

I'm trying to figure out how I could modify the second formula to say that i only want to count it if they are not listed in the lead column.

Any help would be much appreciated!

Thanks

Lynn

Werner Gerstacker

Hi @Lynn Meadow ,

You only need to add a condition to your second formula - see below:

I put it all in one sheet just so it's easier to see.

Count Lead =COUNTIFS(Lead:Lead, $Name@row, $Status:$Status, "In Progress")

Count CPE =COUNTIFS(CPE:CPE, Name@row, Lead:Lead, <>Name@row, Status:Status, "In Progress")

Count IM =COUNTIFS(ID:ID, Name@row, Lead:Lead, <>Name@row, Status:Status, "In Progress")

Count MM =COUNTIFS(MM:MM, Name@row, Lead:Lead, <>Name@row, Status:Status, "In Progress")