Using COUNTIF/FIND function for how many times a name appears in column on separate sheet

AubreyFord

I'm trying to build a dynamic formula. The goal is to count how many projects a resource is on for each month and reflect that number in a cell. Ideally it would look at the names from the 'Resource' column and reference if that name is found in the 'Project Team' column (on another sheet). The project team column can/does contain multiple contact names. I've created example tables below:

Sheet 1 Sheet Two

Resource (column name) Project Team (column name)

person 1 person 1, person 2

person 2 person 3, person 1

person 3 person 1, person 2, person 3

The formula I'm trying to build is in sheet 1 and within the cell I want to reflect (for example) how many times person 1's name appears in the 'project team' column without having to FIND a specific name (so that i can apply it across the sheet without having to update the cell with each person's name on the sheet.

I hope that made sense. If you think you can help please let me know if I can provide additional clarity to aid in creating the formula!

ker9

Hi @AubreyFord

Presuming the following:

1) Project Team column is multi-select on Sheet2

2) Resource column has one name (is not multi-select) on Sheet1

3) The formula is on the same sheet as the Resource column (Sheet1)

=COUNTIFS({Sheet2 ProjectTeam}, HAS(@cell, Resource@row))

Where the reference to Sheet2 is your second sheet containing the Project Team column

Hope this helps!