How to use contains properly?

I am trying to use COUNTIF to check a box. The formula im looking to write would look at my column, "PO #" in sheet 1 and check the "PO" column in a sheet 2 and see if it contains the same number from sheet 1. below is my current formula. I dont get an error but it is not producing the expected result. i have a number of boxes that should be getting checked but are not. any suggestions?

=IF(COUNTIF({Glass Inventory Intake / Form Range 2}, CONTAINS([PO #]@row , @cell )) > 0, 1, 0)

Find more posts tagged with

Accepted answers

DKazatsky2

Hi @Brandon Morales,

For some reason, the CONTAINS does not seem to like the numeric value only scenario when inside the COUNTIFS using multiple ranges/criteria - I don't really understand why. Try this formula and see if it handles everything for you.

=IF((COUNTIFS({Glass Inventory Intake / Form Range 1}, [Glass Type]@row , {Glass Inventory Intake / Form Range 2}, CONTAINS([PO #]@row , @cell ))) + (COUNTIFS({Glass Inventory Intake / Form Range 1}, [Glass Type]@row , {Glass Inventory Intake / Form Range 2}, [PO #]@row )) > 0, 1)

I am thinking this will account for when the PO is only a numeric value and also when it is a string. The CONTAINS should be true for when the PO is numeric and the direct compare should be true when it is a string.

Hope this helps,

Dave

All comments

Cassie L

Hey @Brandon Morales, give this formula a try:

=IF(COUNTIF({PO Column from Sheet 2}, [PO #]@row) > 0, 1, 0)

The issue is that Smartsheet doesn't support CONTAINS() within a COUNTIFS() formula.

Best,

Cassie

Source: Copilot

Brandon Morales

Im trying to mimic the example smartsheet has on their function sheet… this wont work? (see second example below)

DKazatsky2

Hi @Brandon Morales,

Can you show some of the values in {Glass Inventory Intake / Form Range 2} that are not returning the value you would expect?

Hope this helps,

Dave

Brandon Morales

Sure. Lines 1-11 in the first image should all be checked based on the PO # being present in the second image.

Cassie L

@Brandon Morales If I understand correctly, you are looking to return the value TRUE (checkbox) if the Glass Type matches the Glass Type on your tracker AND if the PO for Delivery contains the PO # on your tracker.

Is it possible to ensure that the PO# for Delivery column is an exact match for the PO# column?

If so, two more for you to try: these ones were built using the AI tool for formulas in the right side rail (replace the brackets with appropriate references):

=IF(COUNTIFS({PO for Delivery Range}, [PO #]@row , {Glass Type Range}, [Glass Type]@row ) > 0, 1, 0)

=IF(AND(COUNTIF({PO for Delivery Range}, [PO #]@row ) > 0, COUNTIF({Glass Type Range}, [Glass Type]@row ) > 0), 1, 0)

Best,

Cassie

Brandon Morales

i was able to use a similar formula- im just trying to perfect it using the CONTAINS function to cover all scenarios…

DKazatsky2

Hi @Brandon Morales,

Hope this helps,

Dave

Brandon Morales

Finally! that did the trick. Thank you for that help, this was a tricky one!!

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.