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
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!