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?

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

 

[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 ~