Excel string comparisons with escape character, using boolean, match or sumif

Question

When I was handeling a text-comparison in my database I noticed some odd behavior. In my database I want to SUM values based on a column containing strings. In this example, I want to make a group str and a group txt, with the following string values.

str     2
str1    1
str2    0
txt     1
txt1    2
tx2     3

If I would compare the text with a simpel boolean, i.e. =("str"="str*"), it returns False, because the * is an additional character. This makes sense in some way. However, when I used two other techniques the comparison is handled differently:

First, the following simple SUMIFS function: =SUMIFS(B:B;A:A;"str*") and =SUMIFS(B:B;A:A;"txt*") includes the values at "str" and "txt" respectively, suggesting the comparison is True.

Second, =Match("str*";{Cell containing "str"};0) returns 1, indicating that the comparison also returns True.

Why does the boolean string comparison return False, whilst MATCH and SUMIFS assume True?

Answer 1

In my opinion those two functions apply the wildcard interpretation of the asterisks while your direct comparison formula with '=' takes it as just another (extra) character in the string inside the quotes, so making the two strings different.

While in the function Match(): If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

I hope this makes sense.

Answer 2

Only a limited number of Excel worksheet functions can use wildcard characters to filter results. Functions like COUNTIF ,VLOOKUP, MATCH and others as listed here are some Excel functions that use wildcards.

Apart from the functions listed in the link, wildcard * is treated like a literal when used in the double quotes.