Does anyone know how to use wildcards properly in a formula. For example
(=SUMPRODUCT((A1:A4="6-*")*(C1:C4="ok")*(B1:B4="c-25")). Where "6-*" would
be anywhere the entry in cells A1:A4 begins with the number 6.

Hi!

Try this:

=SUMPRODUCT((ISNUMBER(FIND("6-",A1:A4)))*(C1:C4="ok")*(B1:B4="c-25"))

Biff

"italiavb" wrote in message
...
Does anyone know how to use wildcards properly in a formula. For example
(=SUMPRODUCT((A1:A4="6-*")*(C1:C4="ok")*(B1:B4="c-25")). Where "6-*" would
be anywhere the entry in cells A1:A4 begins with the number 6.

You're a genius man, thanks.

"Biff" wrote in message
...
Hi!

Try this:

=SUMPRODUCT((ISNUMBER(FIND("6-",A1:A4)))*(C1:C4="ok")*(B1:B4="c-25"))

Biff

"italiavb" wrote in message
...
Does anyone know how to use wildcards properly in a formula. For example
(=SUMPRODUCT((A1:A4="6-*")*(C1:C4="ok")*(B1:B4="c-25")). Where "6-*"
would be anywhere the entry in cells A1:A4 begins with the number 6.

So, if the value I'm looking for is text, why doesn't ISTEXT work in place
of ISNUMBER ?


"italiavb" wrote in message
...
You're a genius man, thanks.

"Biff" wrote in message
...
Hi!

Try this:

=SUMPRODUCT((ISNUMBER(FIND("6-",A1:A4)))*(C1:C4="ok")*(B1:B4="c-25"))

Biff

"italiavb" wrote in message
...
Does anyone know how to use wildcards properly in a formula. For example
(=SUMPRODUCT((A1:A4="6-*")*(C1:C4="ok")*(B1:B4="c-25")). Where "6-*"
would be anywhere the entry in cells A1:A4 begins with the number 6.

"italiavb" wrote in message
...
So, if the value I'm looking for is text, why doesn't ISTEXT work in place
of ISNUMBER ?

Because FIND returns a number!

Both FIND and SEARCH return the starting position (character number) of a
substring within a string.

ISTEXT and ISNUMBER have nothing to do with the data type of the search
value. You're testing the returned value of the FIND/SEARCH function.

If the substring is found its position is passed to ISNUMBER which then
evaluates to TRUE. If the substring is not found then FIND returns #VALUE!
then ISNUMBER evaluates to FALSE. So, using ISTEXT in this manner will
always evaluate to FALSE.

Biff

FIND returns #VALUE!
using ISTEXT in this manner will always evaluate to FALSE.

However, you may think that #VALUE! is TEXT and therefore ISTEXT should
evaluate to TRUE, but no, that's not the case. #VALUE! is a LOGICAL VALUE
and not text.

Biff

"Biff" wrote in message
...
"italiavb" wrote in message
...
So, if the value I'm looking for is text, why doesn't ISTEXT work in
place of ISNUMBER ?

Because FIND returns a number!

Both FIND and SEARCH return the starting position (character number) of a
substring within a string.

ISTEXT and ISNUMBER have nothing to do with the data type of the search
value. You're testing the returned value of the FIND/SEARCH function.

If the substring is found its position is passed to ISNUMBER which then
evaluates to TRUE. If the substring is not found then FIND returns #VALUE!
then ISNUMBER evaluates to FALSE. So, using ISTEXT in this manner will
always evaluate to FALSE.

Biff