Sumproduct with certain text

[enshi26]

guys from this formula

=SUMPRODUCT((ISERROR(FIND("MEP",ALL!P11:P15039))=FALSE)*(ISERROR(FIND("L",ALL!D11:D15039))=FALSE)*
(ISBLANK(ALL!J11:J15039)=FALSE)*
(ISERROR(FIND({"1","2","-","A","B","D","E"},ALL!L11:L15039))=FALSE)*(ISBLANK(ALL!B11:B15039)=FALSE))

not only it counts this text {"1","2","-","A","B","D","E"} but it also counts 2&R, B&R & D&R because 2, B & D exist.

what to do so it will not include this text 2&R, B&R & D&R during execution of formula.

thanks

[zbor]

Are {"1","2","-","A","B","D","E"} only characters in cell and can not be part of some string?

[enshi26]

yes, only characters and cannot be part of the string.

[zbor]

And is MEP also one word or part of the string?

[enshi26]

yes MEP is part of a string and L is a character

[zbor]

Here, try this:

=SUMPRODUCT(--(ISNUMBER(FIND("MEP",ALL!P11:P15039))),--(ALL!D11:D15039="L"),--ISNUMBER(MATCH(ALL!J11:J15039,Sheet2!$A$1:$A$7,0)),--(ALL!B11:B15039<>""))

Edit:

I didn't manage with array but you can define list where you want to put your data
(for example: in Sheet2!$A$1:$A$7 you write 1,2,- etc)

[enshi26]

the result didnt give the right one, hmmm

[zbor]

First result or edited?

[enshi26]

now its ok, just define the characters as you write... thanks zbor!