reference and countifs

Julien Lopez

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

Dakota Haeffner

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!

Dakota Haeffner

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!

Julien Lopez

WOW! thank you @Dakota Haeffner . it works perfectly!