SUMIF quirks

ExceljetOct 30, 2026

Hi - Dave here.

Happy Friday!

SUMIF and COUNTIF are two of the most popular functions in Excel. They're easy to use, and they work in every version. But they also have some strange quirks, where normal-looking formulas silently return incorrect results.

For example, see the workbook below. The goal is to total expenses by category, including the expenses with no category (highlighted). The formula in F5, copied down, is:

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

Download the workbook and read the full article

The totals for Food, Travel, and Office are correct. But expenses with no category show $0, even though they should be $140. No error, no warning. Why?

It turns out all eight functions in the *IFS family (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MINIFS, and MAXIFS) have this quirk: An empty criteria cell is treated as zero. This means SUMIF does not match the expenses with no category because it is looking for a zero, not an empty cell.

The *IFS functions behave differently from the rest of Excel because they are built differently: They split each condition into two parts. Once you understand this difference, the strange behaviors make more sense. I just updated my article about these quirks with more examples. See the article for details, and for the fix to the problem above.

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing new content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, for a limited time, you get our Excel Shortcuts video course, free.

Excel formulas

We maintain a list of over 1000 working formulas here.

If you need more structure, we also offer video training.

Have a great weekend!

Dave

The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at a specific Excel formula or function. Sign up on our home page.

Exceljet
P.O. Box 4804
Salt Lake City, UT 84110