Help: formula not evaluating intuitively

[johng89]

Good morning!

I did a bit of searching to solve this on my own but alas, I have been unable to.

I am using a formula to determine how many of these characters exist in a cell:

=SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$B$28,Sheet1!H2)))

The formula is by default evaluating to 3 and I don't know why ('?' '~' and '*' are evaluating to true when they are not present).

When I add any character in the range to the cell, it evaluates to 4 so it is identifying some correctly.

=SEARCH(Sheet2!$A$1:$B$28,Sheet1!H2)which is evaluating to #VALUE! by itself.

The range containing characters I am searching for is formatted as text as is the cell I am searching within. The cell I am searching for the presence of characters within must be maintained as text because I have many instances of leading zeros.

I have recreated the table below and I changed the references to be relative to the table (B2, B3 and B4 are evaluating to 3 in excel).

How do I get this to evaluate correctly?

UPC	Count
885938815014 <tbody> </tbody>	=sumproduct(--isnumber(search($C$2:$D$30,A2)))	a	~
885938815021 <tbody> </tbody>	=sumproduct(--isnumber(search($C$2:$D$30,A3)))	b	`
885938815038 <tbody> </tbody>	=sumproduct(--isnumber(search($C$2:$D$30,A4)))	c	!
		d	@
		e	#
		f	$
		g	%
		h	^
		i	&
		j	*
		k	(
		l	)
		m	-
		n	_
		o	=
		p	+
		q	{
		r	[
		s	]
		t	}
		u	|
		v	\
		w	:
		x	;
		y	"
		z	'
		<	>
		,	.
		?	/

<tbody>
</tbody>

 

[johng89]

***Correction and update***

Correction: I forgot to include a question mark and that would have been 4.

Update: The cases that are causing the "3" to be returned are the tilda, asterisk and a blank. I controlled for the blank by inserting a character from the character map. To convert it to a string literal in the table I preceded the tilda, asterisk and question mark with a tilda so that they appeared in the range as

~~
~?
~*

...and tested adding them to see if they evaluated up with the addition of any of those characters and it worked!

Please close this thread as I have solved it myself after a few hours :P

 

[Jonmo1]

SEARCH allows the use of Wildcards, so * ? and ~ are being treated as such.

Try changing SEARCH to FIND
This however will make it case sensitive, so you will need to add the capital versions of a b c to your list.

 

[johng89]

Correct but they will need to be preceded by a ~