SUMIF quirks

Summary

SUMIF, COUNTIF, and the rest of the *IFS family are workhorse functions used in virtually every business, because they let you apply criteria to common operations like sums, counts, and averages. But they work differently from other formulas in Excel, and their quirks can trip you up and even produce normal-looking formulas that silently return the wrong answer. This article explains each quirk and how to work around it.

Note: Earlier versions of this article called these the RACON functions, short for "range-based conditional" functions, a name suggested by a reader many years ago. It never really caught on, so I've retired it in favor of the more common name, the *IFS functions.

Table of Contents

• Introduction
• The *IFS family
• Criteria are split in two
• Ranges are required
• An empty criteria cell is read as zero
• Leading zeros are ignored
• Other quirks
• Conclusion

Introduction

SUMIF and COUNTIF first appeared in Excel 5 in 1993, when Excel was much more focused on worksheet ranges than arrays. They offered a simple way to perform conditional calculations without array formulas: give Excel a range to examine, a condition to apply, and, for SUMIF, a range to sum.

The unusual criteria syntax probably has roots in Excel's older database functions, like DSUM and DCOUNT. These functions use a criteria range on the worksheet, with conditions like >5 entered in cells below a header. SUMIF and COUNTIF package the same idea into function arguments. Instead of writing a logical expression like B5:B14>5, you provide the range and condition separately. And because >5 isn't a valid expression by itself, it must be entered as text: ">5".

This design also explains several quirks discussed below: you can't transform a range before using it, references to closed workbooks cause problems, and an empty criteria cell is read as zero. Nevertheless, the functions were popular. Microsoft introduced SUMIFS, COUNTIFS, AVERAGEIF, and AVERAGEIFS in Excel 2007, and MINIFS and MAXIFS in 2016, all built the same way.

Today, Excel has much better support for arrays and logical expressions, but the *IFS functions still follow conventions established more than 30 years ago.

The *IFS family

There are eight functions in the *IFS family, listed in the table below. The examples in this article use SUMIF and COUNTIF, but the quirks apply to all eight, except where noted.

COUNTIF, SUMIF, and AVERAGEIF can only apply a single condition. The plural versions (COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, and MAXIFS) can apply multiple conditions.

Although the name suggests otherwise, the IFS function is not part of this family. IFS is designed to run multiple logical tests, written in standard formula syntax, without nesting IF functions.

Criteria are split in two

In most of Excel, a logical test is a single expression. To check whether the value in B5 is greater than 5, you write:

=B5>5 // returns TRUE or FALSE

Nothing special, right? The expression makes perfect sense, and you can copy it down to test every value in a column. Now suppose you want to count the values in B5:B14 that are greater than 5. With the COUNTIF function, the formula in E5 is:

=COUNTIF(B5:B14,">5")

Notice anything strange about this formula? What's ">5" doing in quotes? Isn't 5 a number? Yes. Even though we want Excel to handle 5 as a numeric value, we must wrap ">5" in quotes because >5 by itself is invalid as an argument.

This happens because COUNTIF splits the logical expression B5:B14>5 into two parts: the range B5:B14 and the condition >5. Somewhere behind the scenes, Excel reassembles the two parts:

B5:B14>5 // reassembled expression

Things get even weirder when the criteria involves another cell. To count values greater than the number in D5, the formula in E5 is:

=COUNTIF(B5:B14,">"&D5) // complicated!

We are counting numeric values, but we need both concatenation and quoted text? Yes. But note the cell reference itself, D5, is not quoted. Try explaining this to someone just starting out in Excel.

Text criteria are a little simpler. The equals operator (=) is implied, so both of these formulas count cells equal to "apple":

=COUNTIF(B5:B14,"apple")
=COUNTIF(B5:B14,"=apple")

The same goes for numbers: 5 and "=5" both count cells equal to 5.

To summarize, because the *IFS functions split logical expressions into two parts, they require a unique syntax:

1. Criteria that include a logical operator must be enclosed in double quotes ("").
2. The equals operator (=) can be omitted (or not).
3. Criteria based on another cell must use concatenation.

Ranges are required

A second key difference is that range arguments must be actual ranges. You can't give an *IFS function an array instead.

At first blush, this may not seem like a big deal. After all, most data is already on a worksheet, so why not use a range? The catch is that you can't alter the values in a range before the function tests them. Dates are a good example. To check whether the date in B5 is in June, you can use the MONTH function:

=MONTH(B5)=6 // returns TRUE or FALSE

Copied down, this formula returns TRUE for the two dates in June:

Now, suppose you want to count the dates in June with COUNTIF. You might think you can use MONTH inside COUNTIF like this:

=COUNTIF(MONTH(B5:B14),6) // nope

While this looks perfectly reasonable, Excel won't even let you enter the formula. Instead, it shows a generic "There's a problem with this formula" message:

However, that message is misleading. The problem is not that you are missing a comma or something like that. The real problem is that MONTH returns an array of month numbers, and COUNTIF only accepts a range.

Spill ranges work fine in *IFS functions, since a spill range is a range on the worksheet. For example, if C5 contains =MONTH(B5:B14), the month numbers spill into C5:C14, and =COUNTIF(C5#,6) returns 2.

Because of this limitation, to count dates in June with the COUNTIFS function, you need two conditions, one for the first day of the month and one for the last:

=COUNTIFS(B5:B14,">="&DATE(2026,6,1),B5:B14,"<="&DATE(2026,6,30))

To avoid some of this busy work, we can adapt the formula to use a cell reference. In the worksheet below, the first of the month is in cell D5. The formula in E5 uses this date directly for the first condition and the EOMONTH function to calculate the last day:

=COUNTIFS(B5:B14,">="&D5,B5:B14,"<="&EOMONTH(D5,0))

This is more convenient, but we still can't avoid the monkey business of concatenation. And note that both formulas count dates in June 2026, not June in any year, which may or may not match your intention.

How can you count dates in June without these limitations? In Excel 2021+ and Excel 365, which support dynamic arrays, the SUM function can do it with Boolean logic. The formula in D5 is:

=SUM(--(MONTH(B5:B14)=6))

Notice the logical test, MONTH(B5:B14)=6, is exactly the same test we used above to check one date, applied to the whole range at once. The result is an array of TRUE and FALSE values, and the double negative (--) converts them to 1s and 0s so SUM can add them up.

The FILTER function uses the same logic:

=COUNT(FILTER(B5:B14,MONTH(B5:B14)=6))

And in Excel 365, the GROUPBY function can count the dates in every month at once:

=GROUPBY(MONTH(B5:B14),B5:B14,COUNT,0,0)

In Excel 2019 and earlier, use the SUMPRODUCT function, which has handled arrays natively since Excel 3 in 1990:

=SUMPRODUCT(--(MONTH(B5:B14)=6))

For more on this approach, see Why SUMPRODUCT?

An empty criteria cell is read as zero

This quirk catches people who build summary tables. In the worksheet below, the formula in F5, copied down, is:

=SUMIF($B$5:$B$14,E5,$C$5:$C$14)

Notice the ranges are locked to prevent them from changing as the formula is copied down, while the criteria (E5) is relative so that it will change. The totals for Food, Travel, and Office are correct. However, E8 is empty, and the three expenses with no category (highlighted) add up to $140. So why does SUMIF return zero in F8 instead of $140?

Well, it turns out that SUMIF treats an empty cell differently depending on where it is. In the range, an empty cell is empty, and it does not match 0. But when the criteria refers to an empty cell, SUMIF reads it as 0. So it looks for cells that contain 0 and finds none. If the empty cells in column B held zeros instead, SUMIF would sum those rows.

An ordinary logical expression doesn't have this problem. B5:B14=E8 returns TRUE for the three empty cells, so =SUM((B5:B14=E8)*C5:C14) returns $140, as expected.

The fix is to join an empty string ("") to the reference:

=SUMIF($B$5:$B$14,E5&"",$C$5:$C$14)

Why does this work? An empty cell isn't really zero. Excel converts it to whatever the formula needs: 0 in a math operation, and an empty string ("") in a text operation. The ampersand (&) joins text, so E5&"" converts the empty cell to "" before SUMIF sees it, and "" as criteria matches empty cells. Without the ampersand, SUMIF gets Excel's default conversion, 0, the same value you see when a formula like =E8 returns an empty cell. When E5 contains text, a number, or a date, joining an empty string changes nothing. All eight *IFS functions work this way: when the criteria is an empty cell, they ignore empty cells and match only cells that contain zero.

The devil is in the details. In the example above, the quirk is that an empty cell reference as criteria does not match empty cells. However, you can write a SUMIF formula that sums values associated with empty cells, using either "" or "=" as criteria. For example, both =SUMIF(B5:B14,"",C5:C14) and =SUMIF(B5:B14,"=",C5:C14) return 140 in the worksheet above. The difference is that "" also matches formulas that return an empty string, while "=" matches only cells that are truly empty. In my opinion, it is this kind of nuance that makes the *IFS functions harder to use correctly than most people realize. Worse, it creates an easy trap to fall into, where normal-looking formulas silently return incorrect results.

In Excel 365, you can avoid this trap altogether with GROUPBY. Instead of matching each category in a criteria cell, GROUPBY builds the whole summary table directly from the data. The formula in E5 is:

=GROUPBY(B5:B14,C5:C14,SUM,0,0)

GROUPBY finds every category in B5:B14, including the empty one, and sums the matching amounts in C5:C14. The two zeros at the end turn off headers and the grand total.

Notice the label for the empty category is 0. Any formula that returns an empty cell's value displays 0 (=E8 does, and so does FILTER), but here the zero is only a label: the empty cells were grouped correctly, and $140 is right. In SUMIF, the zero changes which cells match, so the answer is wrong. If you want to force GROUPBY to display an empty label instead, use B5:B14&"" for the categories.

Leading zeros are ignored

The *IFS functions treat text that looks like a number as a number. This causes trouble with codes and IDs that have leading zeros. In the worksheet below, the codes in column B are text, and the formula in F5 is:

=SUMIF(B5:B14,E5,C5:C14)

Only three rows (highlighted) have the code 0450, and their quantities add up to 10. But SUMIF returns 29, because it reads 0450 as the number 450, and it does the same with every code in column B. So it also matches 450 and 00450. As with the empty criteria cell, there's no error and no warning, just a wrong answer. And joining an empty string won't help, because E5 already contains text.

The fix is to compare the codes directly with Boolean logic. The formula in F5 is:

=SUM((B5:B14=E5)*C5:C14)

An ordinary comparison treats text as text, so B5:B14=E5 is TRUE only for the three cells that contain 0450. Multiplying by the quantities in C5:C14 zeros out the other rows, and SUM returns 10. In Excel 2019 and earlier, use SUMPRODUCT instead of SUM. For more examples, see Count numbers with leading zeros.

Other quirks

The *IFS functions have a few more quirks worth knowing about:

• They miscount numbers longer than 15 digits, such as credit card numbers, even when the numbers are stored as text.
• Comparisons like ">100" test numbers only and skip text, while an ordinary formula treats any text as greater than a number.
• Wildcards only match text. =COUNTIF(B5:B10,"1*") counts the text "1abc" but not the number 123. To match numbers by their first digits, see Count numbers that begin with.
• Criteria longer than 255 characters return a #VALUE! error. To match long text, compare directly with a formula like =SUM(--(B5:B10=E5)).
• SUMIF and AVERAGEIF quietly resize sum_range and average_range to match range, starting from the top-left cell. A sum range with the wrong size or extra columns gives a result that looks normal but is wrong. The other functions return #VALUE! when the ranges differ in size.
• A range in another workbook only works while that workbook is open. Otherwise, the formula returns #VALUE!.
• 3D references are not allowed for range arguments.

Conclusion

The *IFS functions are useful and, in some slightly twisted way, "easier" for many users. For simple situations, they work great. But when you run into one of the quirks above, what seemed easy suddenly becomes hard to understand and explain. You may want to use a more modern approach in the following situations:

• You need to test a calculated value, like the month of a date.
• A criteria cell might be empty.
• You need to match codes or IDs with leading zeros.
• You need to match long text or long numbers, like credit card numbers.

The beauty of dynamic array formulas is that logical expressions work the same way everywhere, with the same clean syntax and no quirky exceptions. In many cases, a modern approach is simpler and more flexible.

To learn more about modern formulas in Excel, browse our dynamic array formula examples. If you'd like more structure, including practice worksheets, see our video course on Dynamic Array Formulas.