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

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭
    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

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭
    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!

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!