How do i use IF and CONTAINS properly?

Im truing to write a formula that searches another sheet to see if one column has a cell that contains the same PO # and glass type. If yes, i would the box to be checked. If no the box should remain unchecked. Any ideas? Below is what my formula currently looks like:

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

Find more posts tagged with

Accepted answers

Darren Mullen

@Brandon Morales Correct, CountIFS gives you a count, so if is 1 or 0 then you get lucky and it checks. You'll have to force it to a boolean like this using an IF statement to for a 1 (true) or 0 (false)

=if(COUNTIFS({THE SHEET YOU'RE REFERENCING}, [PO #]@row , {THE SHEET YOU'RE REFERENCING}, [PO #]@row , [Glass Type]@row ) > 0, 1, 0)

All comments

jjg279

I think you just need to move over your AND:

=IF(AND({Glass Inventory Intake / Form Range 2}@row = [PO #]@row , {Glass Inventory Intake / Form Range 1}@row = [Glass Type]@row , 1, 0)

Brandon Morales

Still unparseable… also, is there a way to use contains? im hoping to find the PO number regardless of wheter the entry has "PO" in front of the number or just the number by itself…

heres my formula:

=IF(AND({Glass Inventory Intake / Form Range 2} @row =[PO #]17,{Glass Inventory Intake / Form Range 1} @row =[Glass Type]@row ),1,0)

JR90

The countifs formula should work, try this in your received column

=COUNTIFS({THE SHEET YOU'RE REFERENCING}, [PO #]@row, {THE SHEET YOU'RE REFERENCING}, [PO #]@row, [Glass Type]@row)

It should check the box when it meets the 2 criteria and it will be unchecked when it does not. I tried this in a test sheet and it worked good

Brandon Morales

that kind of worked. Just 2 problems:

One of the lines I get "BOOLEAN EXPECTED", I believe this is because there are more than one times that the criteria are me

Also, there are bunch of lines that should be checked but are not because instead of the PO being "61569" it is entered as "61569B". That's why originally was trying to use "Contains".

Any ideas on how to fix this?

Darren Mullen

=if(COUNTIFS({THE SHEET YOU'RE REFERENCING}, [PO #]@row , {THE SHEET YOU'RE REFERENCING}, [PO #]@row , [Glass Type]@row ) > 0, 1, 0)

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.