reference and countifs
Hi,
i have a sheet with with multiple contact names "Employees" and another for dates. this is used to identify who will work on specific dates.
i have other sheets per employee to match multiple informations at the same place. this sheet is build as one row per calendar date.
i use this forluma:
=IF(COUNTIFS({Weekend / Holiday Staff name}, [Main Tech]@row, {Weekend / Holiday Staff date}, Start@row) > 0, 1)
"Main Tech@row" and "Start@row" are from the employee sheet.
it works well if there is only one name in the cell on both sheet but not working when one cell as many names.
is there a way to match a cell with many contact names to a one contact name as reference?
thank you for your help
Best Answer
-
Screenshots would help but if I'm understanding this right the {Weekend / Holiday Staff name} is a contact column type and the [Main Tech]@row fi the name if who you are trying to count. Usually I need to use a nested HAS statement within the COUNTIFS for Contact Columns.
No promises but maybe try something like:
=IF(COUNTIFS({Weekend / Holiday Staff name}, HAS(@cell, [Main Tech]@row), {Weekend / Holiday Staff date}, Start@row) > 0, 1)
If that doesn't work screenshots help me understand the layout!
Answers
-
Screenshots would help but if I'm understanding this right the {Weekend / Holiday Staff name} is a contact column type and the [Main Tech]@row fi the name if who you are trying to count. Usually I need to use a nested HAS statement within the COUNTIFS for Contact Columns.
No promises but maybe try something like:
=IF(COUNTIFS({Weekend / Holiday Staff name}, HAS(@cell, [Main Tech]@row), {Weekend / Holiday Staff date}, Start@row) > 0, 1)
If that doesn't work screenshots help me understand the layout!
-
WOW! thank you @Dakota Haeffner . it works perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!