SEARCH function with multiple conditions Solved

Question

Hello,

In column A, I have several thousand cells with text. When I ask Excel to indicate if the term D1 exists in the cell, I write =SEARCH("D1",A1) and it returns a number if it finds the term D1 in A1. Then I filter all the cells containing a number, which gives me all the cells that have D1 in their text.

Now, if I ask Excel to return a number if it finds either D1 or D2 in the cell, it doesn't work anymore. I tried =SEARCH(OR("D1","D2"),A1) and other techniques, but it doesn't work.

Does anyone have a solution?

Best regards

Vaucluse · Accepted Answer

Hello

if you have a large number of reference texts to search, you can:
make a list of these references from D1 to D10 for example (without any empty cells)

in C1, the formula to drag down to the useful height

=SUMPRODUCT((COUNTIF(A1,"*"&$D$1:$D$10&"*"))*1)

which returns 1 if A contains a text from D1:D10 or 0 otherwise.

pay attention to all the signs

best regards

--
To err is human, to persist is diabolical

Gyrus · Answer

Hello,  A proposal =OR(ISERROR(SEARCH("D1",A2)),ISERROR(SEARCH("D2",A2))) Best regards

Erakmur · Answer

I am currently trying the following formula based on your instructions:

=SUMPRODUCT(OR(IFERROR(SEARCH("CJD",Extraction!$A$2:$A$10000),0),IFERROR(SEARCH("MES",Extraction!$A$2:$A$10000),0))*(Extraction!$B$2:$B$10000={"DB","CO","DF"}))

But unfortunately, it is not working.

I would like the count of cells from A2 to A10000 containing CJD or MES in the cells of B2:B10000 containing DB, CO, or DF to be shown.

This formula works very well for a single condition of the SEARCH function, but not for two.

I thought my initial request would be sufficient, but I realize it is not.

Can you help me?

Sincerely

Raymond PENTIER · Answer

Hello Erakmur.  I think things are getting a bit heated, and you don't even have time to proofread... What did you mean by "I would like the number of cells from A2 to A10000 containing CJD or MES, where the cells from B2:B10000 contain DB or CO or DF to appear"? Would it be "I would like to know the number of cells in column A containing CJD or MES, with the adjacent cell in column B containing DB, CO, or DF"?  In that case, a solution could be =AND(OR(IFERROR(SEARCH("CJD",A:A),0)>0, IFERROR(SEARCH("MES",A:A),0)>0), OR(IFERROR(SEARCH("DB",B:B),0)>0, IFERROR(SEARCH("CO",B:B),0)>0, IFERROR(SEARCH("DF",B:B),0)>0)  Best regards. Retirement is nice! Especially in the Caribbean... :-) Raymond (INSA, AFPA, CF/R)

Mytå · Answer

Re the Forum

I had really misread, formula corrected according to the Vaucluse principle

=AND(SUMPRODUCT((COUNTIF(A2,"*"&{"CJD","MES"}&"*")))>0;SUMPRODUCT((COUNTIF(B2,"*"&{"BD","CO","DF"}&"*")))>0)

Mytå

--
Thank you for following up on your question, we are not robots...
“If debugging is the art of removing bugs, then programming must be the art of creating them.”

Erakmur · Answer

Hello,

Here is the attached file (I should have started with this from the beginning).

http://cjoint.com/?DIdllgh0PyW

In the calculations, I can't count the EXT since it's the only one that includes several parameters in the extraction tab like CDJ, MES, LOG, ACC, MAF in column A.

It's a number I need.

Best regards

tontong · Answer

Hello,
The OR is translated by a + in matrix.
Try a formula like this:
=SUMPRODUCT(((ISNUMBER(SEARCH("CDJ",A2:A1000))+ISNUMBER(SEARCH("MES",A2:A1000))>0)*(F2:F1000)))

Note: I have reduced and simplified the arguments for easier reading.
The >0 is there to count only one value from F even if A contains CDJ zzzzMESxxxx.

If each cell can only contain one value:
=SUMPRODUCT(((ISNUMBER(SEARCH({"CDJ","MES","LOG"},A2:A1000)))*(F2:A1000)))
To be verified!

Erakmur · Answer

The following formula works:

=SUMPRODUCT(NOT(ISERROR(SEARCH("CJD",Extraction!$A$2:$A$10000))) * (MONTH(Extraction!$C$2:$C$10000)=1) * (Extraction!$F$2:$F$10000={"DB","CO","DF"})) + SUMPRODUCT(NOT(ISERROR(SEARCH("MES",Extraction!$A$2:$A$10000))) * (MONTH(Extraction!$C$2:$C$10000)=1) * (Extraction!$F$2:$F$10000={"DB","CO","DF"})) + SUMPRODUCT(NOT(ISERROR(SEARCH("ACC",Extraction!$A$2:$A$10000))) * (MONTH(Extraction!$C$2:$C$10000)=1) * (Extraction!$F$2:$F$10000={"DB","CO","DF"})) + SUMPRODUCT(NOT(ISERROR(SEARCH("MAF",Extraction!$A$2:$A$10000))) * (MONTH(Extraction!$C$2:$C$10000)=1) * (Extraction!$F$2:$F$10000={"DB","CO","DF"})) + SUMPRODUCT(NOT(ISERROR(SEARCH("LOG",Extraction!$A$2:$A$10000))) * (MONTH(Extraction!$C$2:$C$10000)=1) * (Extraction!$F$2:$F$10000={"DB","CO","DF"})) + SUMPRODUCT(NOT(ISERROR(SEARCH("EXT",Extraction!$A$2:$A$10000))) * (MONTH(Extraction!$C$2:$C$10000)=1) * (Extraction!$F$2:$F$10000={"DB","CO","DF"}))

But you realize that it will be extremely heavy especially since there would still be conditions to add. I keep repeating it while changing the condition. I want a simpler formula.

tontong · Answer

=SUMPRODUCT((ISNUMBER(SEARCH({"CJD","MES","ACC","MAF","EXT","LOG"},Extraction!$A$2:$A$10000))*(MONTH(Extraction!$C$2:$C$10000)=1)*((Extraction!$F$2:$F$10000="DF")+(Extraction!$F$2:$F$10000="CO")+(Extraction!$F$2:$F$10000="DB")))

It would be beneficial to place the month numbers in cells to make the formula copyable.

controliarnaud · Answer

Hello,  I think it would be better to write:  =search("D1", A1) + search("D2", A1) the addition of the 2 ...

SEARCH function with multiple conditions

10 réponses

Texture issues in enshrouded

Edit ad on leboncoin for free

Disable hard drive password

Duration of unpaid filing with telephone operators

Where to rewatch temptation island...

Black screen at wakfu launch

My pc starts up correctly but my screen won't turn on: what should i do?

Voicemail icon that remains displayed

Is onfancy.fr reliable?

Is the sports shoes website reliable?