Functions in a contact column

Options

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

Answers

  • Werner Gerstacker
    Werner Gerstacker ✭✭✭✭✭
    Options

    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")

  • Lynn Meadow
    Lynn Meadow ✭✭✭✭
    Options

    This was amazing!!!! Thanks for helping me out. I am still trying to understand all of the functions and the syntax. You saved the day!!! Appreciate the help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!