CODEWITHSUNDEEP
google-sheetsexcel-formulagoogle-sheets-formula
Matthew Smith
Matthew Smith

Reputation: 25

Google Sheets- Countif Formula Count Cells specific text Multiple Criteria

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

Upvotes: 1

Views: 388

Answers (1)

Aresvik
Aresvik

Reputation: 4620

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"),),)})

enter image description here

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

enter image description here

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.

Upvotes: 1

Related Questions