Wildcard in SUMPRODUCT for multiple criteria

[DuncanH]

Taking Frank Kabel's solution one step furrther, I would
like to add wildcards into my criteria. I wish to count
the number of simultaneous occurrences of "*a*" in Col A
and "*x*" in Col B where * is a wild card as I do not know
where a or x will occur.
Example : I have a table as follows
Col A Col B required result
a b x y 1
b c y x z 0
c b a y z 0
c a d y x 1
d c z y 0

Result = 2 occurences
None of the formulae I have seen work, I can get "a" 3
times, "x" 3 times, "a" and "x" 6 times, or errors, or
sometimes nearly right when I don't use wildcards.
Any ideas? Thank you

 

[Frank Kabel]

Hi
one way: try
=SUMPRODUCT(ISNUMBER(FIND("a",A1:A1000))*ISNUMBER(FIND("x",B1:B1000)))

 

[JE McGimpsey]

One way:

To reconstruct your table is just:

=COUNTIF(A1,"*a*")*COUNTIF(B1,"*x*")

and copy down.

To get it in one cell:

=SUMPRODUCT(--NOT(ISERR(SEARCH("*a*",A1:A5))),
--NOT(ISERR(SEARCH("*x*",B1:B5))))

 

[DuncanH]

Thanks to you and Frank I now have 2 solutions.
Well done, much appreciated.