Using COUNTIF/FIND function for how many times a name appears in column on separate sheet
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!
Answers
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!