Wildcard characters in countif array formulas

[gregtal]

anyone know how to get wildcards to work in a countif array formula? Here's
what I"m working with:

{=COUNT(IF((Sales!$G$2:$G$969="ax260*")*(Sales!$J$2:$J$969='Rep
Summary'!E5),Sales!B2:B969))}

If I enter a specific value (R600 for example) in the first argument, I get
a solid result. budt adding the wildcard (AX260*) causes a 0 result.

any ideas are greatly appreciated.

 

[T. Valko]

Try this, normally entered:

=SUMPRODUCT(--(LEFT(Sales!$G$2:$G$969,5)="ax260"),--(Sales!$J$2:$J$969='Rep
Summary'!E5),--(ISNUMBER(Sales!B2:B969)))

 

[gregtal]

I'll try it. I should say that not all "root" values being sought are the
same length, but I might be able to find a length that will work in all
instances. Thanks again.

 

[gregtal]

Thanks again. It did work. I'd still like to figure out how to make the
wildcard work, just becase it would save creating many variations of the same
formula, but otherwise, it workd fine. thanks for your prompt reply.

 

[T. Valko]

Replace:

--(LEFT(Sales!$G$2:$G$969,5)="ax260")

With:

--(ISNUMBER(SEARCH("ax260",Sales!$G$2:$G$969)))

Better to use a cell to hold the criteria:

A1 = ax260

--(ISNUMBER(SEARCH(A1,Sales!$G$2:$G$969)))

Note that this will find the substring *anywhere* within the string:

ax260yyy
yyyax260
yyax260yy