use of wildcards

[italiavb]

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.

 

[Biff]

Hi!

Try this:

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

Biff

 

[italiavb]

You're a genius man, thanks.

Hi!

Try this:

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

Biff

 

[italiavb]

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

 

[Biff]

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

 

[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