Wildcard characters in countif array formulas
 

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.
--
Greg

Wildcard characters in countif array formulas
 

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)))

--
Biff
Microsoft Excel MVP


"gregtal" wrote in message
...
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.
--
Greg

Wildcard characters in countif array formulas
 

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.
--
Greg


"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


"gregtal" wrote in message
...
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.
--
Greg

Wildcard characters in countif array formulas
 

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.
--
Greg


"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


"gregtal" wrote in message
...
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.
--
Greg

Wildcard characters in countif array formulas
 

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

--
Biff
Microsoft Excel MVP


"gregtal" wrote in message
...
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.
--
Greg


"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


"gregtal" wrote in message
...
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.
--
Greg