How to Make a Dynamic Formula for Multiple Searchable Values Using CONTAINS or HAS?

System

This discussion was created from comments split from: Contains formula not picking up text.

MHumphrey

I have a similar issue that the fixes described above don't quite cover. I have a dataset with 90+ values that I need to address in the same way as Liam. However, I would like to replace the 'searchable value' described in quotation marks with something more dynamic so I don't have to type 90+ values. I have tried using CONTAINS([Column]@row,@cell), CONTAINS([Column]@row,Range], HAS([Column]@row@cell), and HAS([Column]@row,Range). How do I make this formula dynamic?

I may not be understanding the CONTAINS and HAS formulas fully—please let me know if it's a limitation of these formulas and what the best solution would be. This is my current formula:

=SUMIFS({Contract Value}, {Contractor}, CONTAINS([Contractor]@row,@cell), {Project Status}, OR(@cell = "Portal Pricing Needed", @cell = "Portal Pricing Complete", @cell = "Proposal Submitted", @cell = "Shop Drawings Needed", @cell = "Specialties Needed", @cell = "On Order", @cell = "Specialties Complete")))

Paul Newcome

Are you able to provide screenshots for context?

Georgie

Hi @MHumphrey,

If you haven’t already, take a look at the following resources for more information on the HAS and CONTAINS functions:

• HAS Function
• CONTAINS Function

HAS is designed to work with multi-select contact list columns and multi-select dropdown columns, whereas CONTAINS won’t work with these. 

What happens when you try the different options you provided of HAS or CONTAINS in your formula? If you could provide some examples of the formulas you’ve tried and the results/error messages you’re seeing, that should help us to determine what’s not quite right in the formula. 

It would also be really helpful if you can provide screenshots showing how your sheets are set up, so that we can understand the ranges and exactly what you want to achieve with the formula - could you either provide screenshots of the existing sheets (with any sensitive data hidden), or create some test sheets and provide screenshots of those?

Thanks,

Georgie