Using SUMIF with Text Parameters

In cell A1 Marius has the text value "0E11" and in cell A2 he has the text value "0E99". (Both have an apostrophe in front of them and the cells are formatted as Text.) In cell B1 he has 2 and in cell B2 has 4. If Marius uses the formula =SUMIF(A1:A2,"0E99",B1:B2), Excel always returns 6 when it should return 4. It seems that SUMIF is evaluating the parameters as numeric values instead of as the text they are, so Marius wonders how to force Excel to treat them as text.

First, let's look at the data, but I'll add a couple more rows. (See Figure 1.)

Figure 1. Data for use with the SUMIF function.

Consider the following formula, referencing this data:

=SUMIF(A1:A7,123,B1:B7)

If any cells in A1:A7 contain the numeric value 123, then the corresponding cells in the range B1:B7 are summed. In this case, only a single cell in A1:A7 (cell A7) contains the value 123, so the value of the corresponding cell in B1:B7 (cell B7) is returned, a sum of 3.

The SUMIF function will work just fine with text values, as well. So, consider this formula referencing the same data:

=SUMIF(A1:A7,"abc",B1:B7)

Again, a single cell (A4) contains "abc", so the value of the corresponding cell (B4) is summed, returning 8.

It is a different story, as Marius discovered, if the text value starts with the characters "0E". The following formula returns a sum of 0, even though it should return a sum of 13:

=SUMIF(A1:A7,"0E99",B1:B7)

It is important to emphasize that this only happens if the leading characters are "0E" followed by one or two digits. Thus, the wrong summing occurs if the match is "0E9" or "0E99", but not if you are matching "0E999" or "1E12". Both of these are treated as text and not converted to numeric values.

The reason for this is that the SUMIF function (and, incidentally, the SUMIFS function) automatically convert text values to numeric values, if it is possible to do so. It apparently does this for both the comparison range (A1:A7) and for the comparison value. You can see this if I revisit a formula used just a moment ago, but with one small change:

=SUMIF(A1:A7,"123",B1:B7)

Notice that I put the comparison value in quote marks ("123"), which one could reasonably think would make Excel treat it as a text value. It does not, however, as the summation produces the same result as if the quote marks were not used.

That, of course, brings us back to the problem expressed by Marius—how to make sure that SUMIF utilizes text values as one would expect. One way, of course, is to simply make some change to the values in A1:A7 so that they couldn't be construed as numeric values. For instance, for each of the cells that begins with "E0" you could pre-pend another character, such as "x" or "n" or just about anything. This is then easier for SUMIF to match without the potential for confusion.

Another approach is to make it clear to SUMIF that you are dealing with text. The easiest way to do this is to include, in the comparison value, a wildcard character. For instance, you could use this:

=SUMIF(A1:A7,"?E99",B1:B7)

This matches anything in A1:A7 that has anything in the first character position and ends in "E99". This will work just fine for "0E99", but it would also match "1E99" or "vE99" or "_E99". If this causes an issue, then this use of a wildcard will work, as well:

=SUMIF(A1:A7,"*0E99",B1:B7)

The asterisk matches multiple characters, or no characters at all. So, it matches "0E99" just fine and returns the proper sum. If you had entries that included a bunch of characters followed by "0E99", then this approach would not be appropriate for your needs, and you'll need to go back to using the question-mark approach.

Of course, Excel provides multiple ways to get the results you want, so depending on your data (as in Marius' case), you may want to use a different function. This will provide the correct summation:

=SUM(B1:B7*(A1:A7="0E99"))

That is because the SUM function doesn't do any conversion of text to numeric values. If using older versions of Excel, you'll need to enter this as an array formula (Ctrl+Shift+Enter). If you want a non-array approach, you can use the SUMPRODUCT function in your formula:

=SUMPRODUCT(B1:B7*(A1:A7="0E99"))

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7009) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.