I need help with a formula with 3 range references from another sheet

Options

I have 2 sheets, a source sheet and an update sheet and need help updating a cell when 3 criteria are met from the source.

  1. Employee ID = Employee ID (Range 2)
  2. Date = "11/6/2023" in source sheet (same row) (Range 3)
  3. A calculated field on source sheet = 1 (Range 4)

The third criteria is new so this formula works today:

=IF(COUNTIFS({Time Tracking Range 2}, @cell = [Employee ID]@row, {Time Tracking Range 3}, @cell = "11/6/2023") > 0, 1)

I am wanting to add to this another range (4) on the source sheet is also "1". I have tried several but latest attempt is this:

=IF(COUNTIFS({Time Tracking Range 2}, @cell = [Employee ID]@row)) + COUNTIFS({Time Tracking Range 3}, @cell = "11/6/2023") + COUNTIFS({Time Tracking Range 4}, @row = "1", 1))

Any help or suggestions is much appreciated!! :-)

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!