Index/Collect with Has while meeting specific criteria

Good afternoon community,

I have two sheets where the first (we'll call this one Inventory Math) has a formula written to look at the second sheet (we'll call this one Inventory Cost). In this formula, we are looking for specific criteria on the cost sheet and then subtract from a specific cell on the math sheet. The formula works and looks like this:

=INDEX(COLLECT({2025 Inventory Cost Sheet Range 7}, {2025 Inventory Cost Sheet Range 80}, HAS(@cell, "Harnesses - Check Date (4)")), 1) - [Harness - Check Dates]@row

I'm trying to take this a step further. I need this formula to now incorporate if certain criteria are meet from the math sheet. Meaning, I have a dropdown column with the following options:

Full Inventory - 1 Truck - 5500
Full Inventory - Box Truck 1
Full Inventory - Box Truck 2

I have three columns, one for each dropdown option.

I need the formula to do the math it is doing above, but also look at the dropdown options on the math sheet and only do the math in that column if the corresponding option is selected.

I have tried a number of combinations that include 'IF' or 'CONTAINS' but I can't seem to figure it out.

Find more posts tagged with

Formulas

Accepted answers

All comments

Hollie Green

Try the below if your column on the math sheet is a single select.

=If([Drop down column on math sheet]@row="Full Inventory - 1 Truck - 5500",INDEX(COLLECT({2025 Inventory Cost Sheet Range 7}, {2025 Inventory Cost Sheet Range 80}, Contains("Harnesses - Check Date (4)",@cell)), 1) - [Harness - Check Dates]@row,"")

A C

Thank you for the suggestion, Hollie.

Here is the formula that I plugged in as the one you have resulted in an error.

=IF([Vehicle Type]@row = "Full Inventory - 1 Truck - 5500", INDEX(COLLECT({2025 Inventory Cost Sheet Range 3}, {2025 Inventory Cost Sheet Range 80}, CONTAINS("Harnesses - Check Date (4), @cell")), 1) - [Harness - Check Dates]@row)

I've tried something similar to this but feel we are close. With this formula, it results in an Invalid operation error. But, when I switch the dropdown selection, the invalid error goes away and leaves the cell blank.

A C

Solution:

=IF([Vehicle Type]@row = "Full Inventory - Box Truck 1", INDEX(COLLECT({2025 Inventory Cost Sheet Range 83}, {2025 Inventory Cost Sheet Range 80}, HAS(@, "Harnesses - Check Date (4)")), 1) - [Harness - Check Dates]@row)

I switched the 'CONTAINS' back to 'HAS' and this is now working!

Thanks for the help, Hollie! Knew it was close!

Georgie

Hey @A C,

Thanks for posting your solution - I'm glad you got this resolved!

Georgie

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.