Searching alfanumaric cell contents and returning with a numaric v

[ksean]

Biff,

The result of the formula should be 85

SUMPRODUCT(--(ISNUMBER(SEARCH("blue_frisbee",Master!E3:E800&"_"&Master!F3:F800)))) returned a result of 129, I can't figure out why.

SUMPRODUCT(--(ISNUMBER(SEARCH("blue",Master!E3:E800))),--(ISNUMBER(SEARCH("frisbee",Master!F3:F800))))
also returned a result of 129, I can't figure out why.

SUM(IF(ISTEXT(Master!E3:E800),IF(Master!E3:E800="blue",IF(ISTEXT(Master!F3:F800),IF(Master!F3:F800="frisbee",1)))))
returned a result of 85, which is correct.

By the way in the first two formulas you used a -- in the formula, what
does it do?

Thanks
Kerry

 

[T. Valko]

Hard to say why you're getting incorrect results.

Here's a very small sample file that demonstrates those formulas and they do
return the correct results.

xKSEAN.xls 14kb

http://cjoint.com/?kDrZi15bnj

For an explanation of the double unary (--):

http://xldynamic.com/source/xld.SUMPRODUCT.html