Help with multiple IFContains Statements

Tara Bush

Hi there,

I'm trying to do a formula but it doesn't seem to be working. I basically want to put a Yes or No in a sheet if 2 conditions are met - if one column of another sheet contains the age group 20-30 AND if another column of that sheet contains a code.

=IF(CONTAINS("20-30", {NELFT Talking Therapies - Message Request Range 3}), "Yes", "No") AND (IF(CONTAINS([ODS Code]@row,{NELFT Talking Therapies - Message Request Range 2}),"Yes","No"))

Could you please help?

Heather Duff

@Tara Bush Great! Glad it worked for you. :) Have a great weekend!

Heather Duff

Hi @Tara Bush ,

Just to clarify - are you checking whether the two conditions are met in the same row in the referenced sheet, or that they exist separately somewhere in the sheet? Also, is there a reason you're using the CONTAINS function? If the cells are going to be an exact match (as in, they will only contain "20-30" in the cell or only the same contents as [ODS Code]@row), then there's no reason to fiddle with CONTAINS.

Let me know and we can map it out.

Best,

Heather

Tara Bush

Hi @Heather Duff - Yes that's right, they are in the same row in the referenced sheet/

I chose contains because the 'ODS Code' is not an exact match - it's concatenated with a name, e.g. Surgery-ODS Code. However, the 20-30 would be an exact match,.

Hope that makes sense!

Heather Duff

@Tara Bush

Ok! Let's try this:

=IF(COUNT(COLLECT({NELFT Talking Therapies - Message Request Range 3},{NELFT Talking Therapies - Message Request Range 3},"20-30",{NELFT Talking Therapies - Message Request Range 2},CONTAINS([ODS Code]@row,@cell)))>0,"Yes","No")

This translates to:

If the number of rows that meet these two conditions (Range 3 is equal to "20-30" and Range 2 contains the ODS Code in this row) is greater than zero, show Yes; otherwise, show No.

Let me know if it works for you.

Best,

Heather

Tara Bush

Hi @Heather Duff

Thanks for this. Unfortunately the formula returns 'Yes' for all values, even if the codes aren't in the referenced sheet.

Heather Duff

@Tara Bush

Hmm...Let's try again, then.

=IF(COUNTIFS({NELFT Talking Therapies - Message Request Range 3},"20-30",{NELFT Talking Therapies - Message Request Range 2},CONTAINS([ODS Code]@row,@cell))>0,"Yes","No")

Tara Bush

Sorry @Heather Duff ignore me - when I edit the referenced sheet and re-add them, it works!!

Thanks so much!

Heather Duff

@Tara Bush Great! Glad it worked for you. :) Have a great weekend!

Tara Bush

Thanks so much - you too!