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

✭✭✭✭

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:

• ✭✭✭✭✭✭

You would keep the same syntax as your first formula and just add the next range/criteria set within the same COUNTIFS as the other 3 range/criteria sets.

• ✭✭✭✭✭✭

You would keep the same syntax as your first formula and just add the next range/criteria set within the same COUNTIFS as the other 3 range/criteria sets.

• ✭✭✭✭

Paul,

Final formula:

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

Worked perfectly, thank you!!!!

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭

Hello!

My search for a solution led me to this page, and I am hoping it's just a few tweaks.

I have a reference sheet with the following columns: ID, Name, Product, Note.

I need to pull the information in the Note column, based on ID, Name, Product on main sheet. I've tried multiple Index Match formulas on these pages but they have not worked. Any help is appreciated!

Thank you!

• ✭✭✭✭✭✭

@brdowney You are going to want to look into an INDEX/COLLECT.

• ✭✭
edited 11/14/23

Thank you Paul! INDEX/COLLECT does work, but it is not working in instances where I reference Columns with numbers---any ideas?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!