Google Sheets- Countif Formula Count Cells specific text Multiple Criteria

Question

I currently have a google sheet in which I count values in Column L against a reference sheet 'Ref' and then against a status Column W

IF(AND(COUNTIF(Ref!$A$1:$A$100,L2),W2 <>"Cancelled",W2 <>"Postponed",W2 <> "Dropped"),"Y", "N")

However the values have now changed in Column L. Instead of defined values in each cell they will be a string of values in each cell, the permutations will be too great to enter into my reference sheet.

How do I still count the values in column L to correctly return a 'Y' or 'N'

I have tried

=IF(AND(COUNTIF(L2,{"*LEE*","*LON*","*LAM*"}),W2 <>"Cancelled",W2 <>"Postponed",W2 <> "Dropped"),"Y", "N")

=IF(AND(COUNTIF(Ref!$A$1:$A$100,"*"&L2&"*"),W2 <>"Cancelled",W2 <>"Postponed",W2 <> "Dropped"),"Y", "N")

=IF(AND(COUNTIF(L2,{"*LEE*","*LON*","*LAM*"}),W2 <>"Cancelled",W2 <>"Postponed",W2 <> "Dropped"),"Y", "N")

The values I which need to count will feature one of the following phrases 'LEE', 'LON','LAM'.

The new values that are entered into column L will look like the following

<Area1: Y - LEE>,<Area2: Y - MIL>
<Area3: WF>,<Area4: Y - MUN>
<Area1: YY - MUN>,<Area2: YY - LON>,<Area3: YY-LAM>

So I need to be able to search within the cell for 1 of the 3 phrases.

Any help greatly appreciated

Thanks

Please share a link to a copy of your spreadsheet, with permissions set (when creating the link) to "Anyone with the link..." and "Editor." As it stands, the volunteer contributors here would need to manually enter data into their own sheet before they could even begin, which is a large deterrent. Help us help you — Broly
– Broly, Commented Jun 25, 2021 at 12:20

Aresvik · Accepted Answer · 2021-06-28 10:05:18Z

I have a solution that might work, using arrayformula() in a single cell.

If I've understood correctly, it looks like you're checking Col L to see if cells contain LEE LON or LAM. You're also checking to see that they appear in Ref!$A$1:$A$100.

On your sheet that contains col F and Col W (Status), try this in row 1 (I've used AB1):

=arrayformula({"Check";if(if(L2:L<>"",regexreplace(regexreplace(regexreplace(flatten(query(transpose({if(regexmatch(split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"LAM|LEE|LON"),split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"|")}),"",9^9)),"\ ",""),"(\|)+","\|"),"^\||\|$",),)<>"",if(REGEXMATCH(join(" ",Ref!A2:A),if(L2:L<>"",regexreplace(regexreplace(regexreplace(flatten(query(transpose({if(regexmatch(split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"LAM|LEE|LON"),split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"|")}),"",9^9)),"\ ",""),"(\|)+","\|"),"^\||\|$",),)),if(regexmatch(W2:W,"Cancelled|Postponed|Dropped"),"N","Y"),),)})

To show the working, I've got three helper columns, AD, AE and AF:

AD1 gets LAM LEE or LON from col L:

=arrayformula({"LAM|LEE|LON entry";if(L2:L<>"",regexreplace(regexreplace(regexreplace(flatten(query(transpose({if(regexmatch(split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"LAM|LEE|LON"),split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"|")}),"",9^9)),"\ ",""),"(\|)+","\|"),"^\||\|$",),)})

AE1 checks the values in col AD to see if they appear in Ref!A2:A:

=arrayformula({"LAM|LEE|LON match";if(AD2:AD<>"",REGEXMATCH(join(" ",Ref!A2:A),AD2:AD),)})

AF1 checks if the value in col AE is true, then the status from col W:

=arrayformula({"Check";if(AE2:AE=true,if(regexmatch(W2:W,"Cancelled|Postponed|Dropped"),"N","Y"),)})

The 3 helper columns are combined in AB1.

Collectives™ on Stack Overflow

Google Sheets- Countif Formula Count Cells specific text Multiple Criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related