Issues with char(60) when doing a sumif()

Question

If I am using the sumif builtin function (Excel Microsoft Office Home & Student 2016 version 1708) I get the following behaviour:

amount    description
4         '<Sydney
4         '<Melbourne
=sumif(b1:b3,"'<Sydney",a1:a3)

Sumif returns 8.

This is incorrect. The sumif should return 4.

Please note the use of the ' for string escaping when entering text into the cell.

Any ideas what I am doing wrong here?

Worth noting, the problem seems to be related to using the angle bracket (char(60)). I appreciate that I could get around this issue using the substitute() function - but I would like to understand what the cause of this problem is and how to tell excel to treat char(60) as a string.

Any ideas?

Please note - there are no issues with the sumif if only the hypen (char(45)) is used. E.g:

amount    description
4         '-Sydney
4         '-Melbourne
=sumif(b1:b3,"'-Sydney",a1:a3)

This correctly sums to 4.

Answer 1

The problem lies with

=sumif(b1:b3,"<Sydney",a1:a3)

(I assume the condition parameter is "<Sydney" instead of "'<Sydney" as stated in the question as it will return 0.)

The condition parameter will treat the leading < as "less than" comparison. The condition "<Sydney" will therefore be interpreted as less than the string Sydney by its ASCII value (or Unicode value?). Therefore the strings with leading characters A to R, and < are all "less than" the string Sydney.

To fix the problem, you can change the function as:

=sumif(b1:b3,"=<Sydney",a1:a3)

The = in the condition parameter will tell Excel to "sum only the cell is equal to <Sydney, therefore returning the correct result.

To further illustrate, try these sample data:

          [Col A]  [Col B]
[Row 1]     1          <
[Row 2]     2          A
[Row 3]     4          S
[Row 4]     8          Z

=SUMIF(B1:B4,"<S",A1:A4)    [3]
=SUMIF(B1:B4,"<=S",A1:A4)   [7]
=SUMIF(B1:B4,"=S",A1:A4)    [4]
=SUMIF(B1:B4,">S",A1:A4)    [8]

=sumif(b1:b3,"-Sydney",a1:a3) works as expected, as - does not mean anything and therefore Excel sumif by default compares each cell in B1:B3 with the entire string -Sydney and sum only if the two strings are identical.