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.

Employee ID = Employee ID (Range 2)
Date = "11/6/2023" in source sheet (same row) (Range 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!! :-)

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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.

All comments

Paul Newcome

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.

Tana V

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!!!!

Paul Newcome

Happy to help. 👍️

brdowney

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!

Paul Newcome

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

brdowney

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.