I am trying to create a sumproduct command that allows for a wildcard in one
of the criteria. In the example below I want the term Jone, Jones, Joneston,
etc... to all fit the bill. Is there a way to incorporate a wildcard into
this command?
=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(ApplStaff!$B$2:$B$482="Jone*"))

try
=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(ApplStaff!$B$2:$B$482="Jone*"))
=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(left(ApplStaff!$B$2:$B$482,4)="Jone"))--Don GuillettSalesAid "briank" wrote in ...I am trying to create a sumproduct command that allows for a wildcard inone of the criteria. In the example below I want the term Jone, Jones,Joneston, etc... to all fit the bill. Is there a way to incorporate a wildcard into this command?=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(ApplStaff!$B$2:$B$482="Jone*"))

You can use the FIND function...

=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--ISNUMBER(FIND("Jone",ApplStaff!$B$2:$B$482))

I don't think you can use a wildcard.

"briank" wrote:

I am trying to create a sumproduct command that allows for a wildcard in one
of the criteria. In the example below I want the term Jone, Jones, Joneston,
etc... to all fit the bill. Is there a way to incorporate a wildcard into
this command?
=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(ApplStaff!$B$2:$B$482="Jone*"))

Thanks Don. That works gr8.

"Don Guillett" wrote:

try
=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(ApplStaff!$B$2:$B$482="Jone*"))
=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(left(ApplStaff!$B$2:$B$482,4)="Jone"))--Don GuillettSalesAid "briank" wrote in ...I am trying to create a sumproduct command that allows for a wildcard inone of the criteria. In the example below I want the term Jone, Jones,Joneston, etc... to all fit the bill. Is there a way to incorporate a wildcard into this command?=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(ApplStaff!$B$2:$B$482="Jone*"))

glad to help

--
Don Guillett
SalesAid Software

"briank" wrote in message
...
Thanks Don. That works gr8.

"Don Guillett" wrote:

try
=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(ApplStaff!$B$2:$B$482="Jone*"))

=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(left(ApplStaff!$B$2:$B$482,4)="Jone"))--Don
GuillettSalesAid "briank"
wrote in
...I am
trying to create a sumproduct command that allows for a wildcard inone
of the criteria. In the example below I want the term Jone,
Jones,Joneston, etc... to all fit the bill. Is there a way to
incorporate a wildcard into this
command?=SUMPRODUCT(--(ApplStaff!$A$2:$A$482=$B$5),--(ApplStaff!$B$2:$B$482="Jone*"))