COUNTIFS and OR formula referencing multiple contact columns in another sheet

Hello,


I am trying to come up with a formula that would count the number of active projects for each member of our 6 teams. I am referencing data hosted in another sheet and I have multiple criterias for the COUNTIFS formula :

  • The project has to have an active statut
  • The name of the employee needs to appear in at least one of the 3 contacts column (so I imagine I need to use the OR function, but I didn't manage to make it work)
  • Each project needs to be counted just once, even if the name appears in multiple contact columns.
  • Each contact cell may contain more than one name. If the employee's name is one of them, this project should be counted.
  • I need two formulas : one for the total of projects for each employees and one for the number of project per category (fruits or vegetables) per employee.

Here is a simplified example so we can work a model I would replicate in my real environment. The formulas I am looking for would generate the numbers in a Summary sheet.

Oh, one more thing : the formula needs to reference the "Employees's Name Column" as criteria. I have to apply this to six sections and more than 100 employees so I don't want to change the name in each formula.

So far I have only been able to get this formula working but it doesn't comply with all my requirements described above :

=COUNTIFS({Advisors}; HAS(@cell; [Employee's Name]@row); {Status}; "Active")

Thanks in advance for your help !

Best Answer

  • Miew
    Miew ✭✭
    Answer ✓

    Wow, this is a game changer for me @Leibel S

    Not only does it work like a charm, but I am able to reuse it many different ways in my project. Let's add that I would never ever have figure it out without you. Big thanks !

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Miew

    In your data sheet do as follows:

    1. Add column "LINE-ID" : Auto Number Column (unless you have one already, if yes use the column name in step 2)
    2. Add column "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
    3. Make sure agents, advisors, and coordinators columns are next to each other in your sheet.

    Then your formula to count active through all 3 columns is the below (you will need to add the 2 cross sheet references bolded:

    =COUNTIFS({ROW#}; HAS(INDEX({Agent-Coordinators cross sheet reference}; @cell); [Employee's Name]@row); {Status}; "Active")

  • Miew
    Miew ✭✭
    Answer ✓

    Wow, this is a game changer for me @Leibel S

    Not only does it work like a charm, but I am able to reuse it many different ways in my project. Let's add that I would never ever have figure it out without you. Big thanks !

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!