Checking a column for a single cell that meets all conditions

I have a form that has two multi-select columns and I am trying to find a way to create a chart out of the results. The two multi-selects are "Name" and "Food Given" and then a column for the number of days since the food was offered.

I want to take those results to another sheet and have it determine if each critter can get that food again (there are time limits for how often each food can be given, Broccoli daily, Berries only after two days etc…). Right now I am using a "Join" so I only have to search one column, and was using several "Contains" + "And", but my results are searching the whole column, I need it to only return "Yes" or "No" if all the conditions are met in a single cell.

Into:

Find more posts tagged with

Accepted answers

DKazatsky2

Hi @jjg279,

Give this a try.

=IF(COUNT(COLLECT({Animal Name}, {Animal Name}, HAS(@cell, $[Primary Column]@row), {Food Given}, HAS(@cell, [Column2]$13))) > 0, "Yes", "No")

Hope this helps,

Dave

jjg279

Ah-ha!! That's got it!! Thank you Dave! I didn't know how to tie in the @cell and @row - will definitely use in future! Much mahalos!!

All comments

Michelle Choate 2

This would be a combination of IF, AND, and HAS statements together into one formula. I would write out what you are looking to have returned by each formula, then reverse engineer your formula from there.

jjg279

Hey Michelle! HAS was a great suggestion, hadn't worked with that one before, but I still wasn't able to get the formula to do what I needed. Still returning a positive answer if the name and fruit appears anywhere in the column, not in a single cell. Here was was I started with using HAS:

This was what I started with:

where "Gathered_joined" is a column containing a JOIN of all the results separated by commas:

I have found a less elegant solution using reports and a dashboard that I will run with for now, but any more insight on how to search a column for a single cell meeting all criteria would be very welcome!

DKazatsky2

Hi @jjg279,

Give this a try.

=IF(COUNT(COLLECT({Animal Name}, {Animal Name}, HAS(@cell, $[Primary Column]@row), {Food Given}, HAS(@cell, [Column2]$13))) > 0, "Yes", "No")

Hope this helps,

Dave

jjg279

Ah-ha!! That's got it!! Thank you Dave! I didn't know how to tie in the @cell and @row - will definitely use in future! Much mahalos!!

jjg279

Just in case anyone stumbles on this in future, in order to incorporate the time aspect I wrapped the formula provided by Dave in an OR and then repeated it for the required number of days. Final result was this:

I suspect there may be a more elegant way using < or > but I couldn't get it to work with HAS. Hope this is helpful! And thank you again Dave!

benbeeney

I have the same question! I’m working on a similar setup where I have multiple multi-select fields in my form (like “Name” and “Food Given”) and I’m trying to create a chart or logic sheet that determines when each item can be repeated based on specific cooldown periods. I also tried using “Join” to simplify the search and then “Contains” with “And” conditions, but I ran into the same issue — it checks the entire column instead of evaluating each cell individually. I’d love to know if there’s a more precise formula or method to return a simple “Yes” or “No” result for each row when all the right conditions in a single cell are met.

jjg279

Good morning @benbeeney ! Dave's solution about worked well for me (using the @row function) and I have since gotten more savvy with dates/times - do you have a column with your cooldown periods calculated or is that in your final formula?

Looking at this now, I would use a COUNTIFS/IF to solve this problem:

=IF(COUNTIFS(food column HAS(@cell, "Kiwi"), name column, HAS(@cell, "Joe")>1, "Yes", "No")

Will that help you? Let me know if we can hash it out!

benbeeney

Dave's solution about worked well for me (using the @row function ) and I have since gotten more savvy with dates/times - do you have a column with your cooldown periods calculated or is that in your final formula?
Looking at this now, I would use a COUNTIFS/IF to solve this problem:
=IF(COUNTIFS(food column HAS(@cell , "Kiwi"), name column, HAS(@cell , "Joe")>1, "Yes", "No")
Will that help you? Let me know if we can hash it out!

Thanks so much for the quick reply and for sharing that example! I actually don’t have a separate column for the cooldown periods yet — I’ve just been handling that logic directly in the formula. But I think your approach of using COUNTIFS with IF could definitely make things cleaner and easier to manage, especially since each food type (like Broccoli, Berries, etc.) has a different cooldown rule.

I’ll try setting up a column that tracks the cooldown period for each food and then reference that in the formula as you suggested. The example you shared with HAS(@cell , “Kiwi”) and HAS(@cell , “Joe”) makes sense — I think I just need to adjust it so it evaluates each combination per row instead of scanning the entire column.

jjg279

Howdy @benbeeney! I am glad it was helpful; depending on how many "foods" you have you could keep it all in one formula and wrap it in an OR:

IF(OR(

COUNTIFS({food column} HAS(@cell, "Kiwi"), {Date column}, >TODAY(-7))>1,

COUNTIFS({food column} HAS(@cell , "Berry"), {Date column}, >TODAY(-5))>1

…

), "Yes", "No")

where the number in the TODAY function is your cooldown (assuming it is days - which I realize is a large assumption)

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.