The above image is not surprising at all. SUMIF returns the expected result correctly. We all write SUMIF like this. (Yes, me too. 😅) It works fine most of the time unless we are facing something not so common. Say for example, the text strings all start with “>> “, like the screenshot below:

To be clear, the content in A3 is equal to the content in D2, however the SUMIF is returning the wrong result? Strange…… 🤔

Do you know what’s wrong with the SUMIF formula? It is the same as the SUMIF formula in the first example. Then why does the first one work, and the second one fails? That’s the question!

If you want to know just the quick fix, here’s the formula:

=SUMIF(A2:A4, "=" & D2, B2:B4)

'The secret sauce is "=" &

If you want to know why, you need to know the basics of writing SUMIF.

You may download a sample file to follow along.

First of all, the syntax:

= SUMIF(range, criteria, [sum_range])

It takes two required arguments, range, and criteria, while the final argument is indeed optional although we used to input them all in most cases.

For details of the syntax, pls read the documentation here.

The simplest example would be something like this:

SUM the values that are larger than a certain value, say 20:

In this example the range is A1:A4, and the criteria is “>20”. The subtle importance here is how the criteria is input. It is indeed a text string; enclosed by double quotation marks.

If we want to input the criteria in a cell, i.e. making it a variable, we either input “>20” (no need to input the double quotation marks) in the cell that holds the criteria, or just the numeric value 20 but adjusting the criteria input in the formula. See the screenshot below:

See?! We need to concatenate the larger than operator “>” to the cell reference in order to make SUMIF works. What if the “>”& is omitted in the formula? Excel will assume it’s “equal to” and thus returns 20 as a result. See below:

When we read documentation, don’t miss the “Important” 😁

Important: Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks (“). If the criteria is numeric, double quotation marks are not required.

Now, let’s go back to our problem and try to understand what went wrong!

Any idea?

Want some hints? Let’s look at the example again.

When the criteria input in a cell that starts with an operator (or mathematic symbol) like “>”, Excel interprets that you are trying to compare to a criterion of larger than the value after the symbol “>”. “>20” means larger than 20 in this example.

Yes… so…?

Let’s look at the problem again. The criteria that the SUMIF formula refers to is D2. And look at the content of D2. It starts with the operator “>”.

That means, Excel interprets it wrongly as larger than “> b” instead of equal to “>> b” that you expect.

Let’s test if it is the case by comparing the text values in A2:A4 to larger than “> b“, all returns TRUE.

That’s why the result returned by SUMIF is 6 as all texts meet the criteria larger than “> b”.

Why “>> a” is larger than “> b”?

That’s another interesting question. If you are interested in learning more, I’d suggest you read the blogpost Sorting and Comparing the Excel Way: Code Pages and Collating Sequences – Revised by Charles Williams @ https://fastexcel.wordpress.com

At this point, we know the problem and we need a solution. And the fix is indeed strange forward. Let’s go back to our example again.

When we put numeric value in a cell as the criteria, we need to concatenate the operator to the value to convert it into a text string that is to be fed into the criteria for SUMIF. If we do not input anything, Excel assumes “equal to”. In our problem, the criteria happen to start with an operator “>”. To fix it, we need to restate the operation of “equal to” explicitly by concatenating “=” to the reference cell.

As a result, we fix the SUMIF and get the expected result by the following formula:

= SUMIF (A2:A4, "=" & D2, B2:B4)

Make sense?