SUMIFS Not Working

For what it's worth, when using months with SUMIF/SUMIFS I use the MONTH function to get the month number and base the SUMIF/SUMIFS on the month number, rather than inputting month names to which Numbers, guessing I mean a date-time, assigns the current year.

See this expanded Personal Budget template (Dropbox download) from a few years ago. It does essentially the same thing as the example posted in the original post above: summaries expenses by month and category.

Generally it's best (in both Excel and Numbers) to avoid "hard-wiring" specific criteria into a formula. Put the criteria in cell(s) and have the SUMIFS refer to those.

SG

I can't tell if this is the problem in this particular case, but I've found that sometimes Numbers is too smart in recognizing dates.

For example, when I enter month names Numbers interprets those as date-time, as shown here. Perhaps that is the case for your entries in the Month column in the Expense table.

Because the actual value in the cells are date-time (even though formatted to just show the month name) a SUMIFS function won't find a match against, say, "January".

So an easy solution is to just type a ' (an apostrophe) first when entering the month names. That tells Numbers you mean Text and not date-time. (Or if you prefer, you can also first format the cells as Text and then enter the month names without the ' ).

Once you've done that, the SUMIFS should calculate correctly.

SG

As mentioned above, I was having this same SUMIFS problem, where previously working functions were returning zero.

In particular, when using "Pop-Up Menus" cells. I first noticed that replacing the Pop-Up Menus with text returned the functions back to their working state. And then I noticed if I simply deleted and rebuilt the pop-up Menu, it started working again.

I hope that helps solve your problem. Weird bug.

projectpage wrote:

For what its worth, highlight the months column and changing the Cell Format to Text and then back to a Pop-Up Menu fixed the issue

Yes, that is exactly what I was trying to articulate above.

Numbers date recognition is often convenient but sometimes it is too smart!

But fortunately, as you have discovered, there are easy ways to tell it to take you literally. Usually that involves explicitly formatting as Text.

I use both Numbers and Excel. If you're working with large datasets you'll be much happier with Excel. It's designed to handle tens of thousands of rows with ease. And its Pivot Tables are are a powerful tool to crunch the data without writing formulas.

Numbers, on the other hand, is very handy and intuitive for small to medium-sized projects. Its Category function (somewhat similar to Subtotals with some aspects of Pivot Tables in Excel) is very powerful, though you'll find things become sluggish if you have to deal with many thousands of rows.

SG

Umh, yes, it's happened to me. Until I remembered Numbers is sometimes too helpful in guessing date-times.

Make sure to format cells as Text when you want Numbers to interpret what you input literally. Leave them formatted as Automatic if you want Numbers to guess that by entering, say, January that you want Numbers to you mean January 1 of the current year.

January in a cell formatted as Automatic can mean January 1, 2019, then change to mean January 1, 2020 in the new year. So you're going to get inconsistent results with SUMIF and SUMIFS.

The functions are working just fine on my machine.

SG

I'll save you the trouble. It's not the Pop-Up Menu's fault. The only possible explanation is that the SUMIFS statement takes the value "=October" and turns it into 10/1/CURRENTYEAR. Which is a little crazy.

When you touch your previously-working SUMIFS cell in 2020, it converts the parameter "=October" into 10/1/2020, the new year, causing it to return zero (and not technically fail with an error).

In my opinion, this isn't how a month parameter in quotes with an equal sign in front of it should be handled by Numbers.

The easy way to fix this is to simply re-type your pop-up menu months, literally in the year 2020 (as stated above), so that they become 10/1/2020 as well, and then the SUMIFS will pick it up.

I can't believe I just asked this question five minutes after you. I'm having the same problem when using SUMIFS to call values from cells that are in "pop-up menu" format. They previously worked and they are now returning zero. If I change the formats of these cells to "text" with the correct values, the functions work again.

When I go back to older documents and check the cells, that action causes the functions to return zero.

One little nitpick I would mention for you is that I add equal signs in front of Dining and January. "=Dining" and "=January".

2019 MBP Catalina.

Just to add to the pile-on of observations:

1. These were previously working functions.
2. When these functions stopped working, they started to return zero, not the red exclamation triangle.
3. When I pull up an old document, and click on a cell that displays a correct SUMIFS return value, it then switches to zero. In other words, the act of clicking on a cell caused the function to fail.

This doesn't appear to be a user entry problem, but rather a software glitch. I imagine if you don't have documents with SUMIFS that are failing, this might be a hard error to reproduce. I can't pin it down to a particular Pages update, or maybe it does have something to do with the new calendar year. The observation that the cell first has to be text is an interesting one. I think for me, deleting the Pop-Up Menu values and then retyping them is what worked.

quinn,

I think the takeaway is that, when you are using a month as a test value in a SUMIFS function, there's a chance that Numbers is sneakily assigning a year to your month test value in your function. When you say something like "January". Just like it attaches a year to the month values in a pop-up menu.

That's the only possible explanation for why cutting and pasting previously working tables after the new year didn't work for OP. It also explains how when I revisit my 2019 documents and inspect my SUMIFS functions, they start to fail. Only after I inspect them.

It's definitely a date quirk. I tested my other word-based categories, like "type of expense," and they could care less what year it is. I think there's a good chance some "previous format" voodoo could work who knows. Either way, it requires a work-around.

My observation is that testing for the value "=October" will not total up expenses from any year; only the current year. Now that I know that, good enough for me. Maybe there are better ways to write date formats in the function. Either way, I think armed with this knowledge it's safe to upgrade. :)

Yes. thanks! The pop-up Menü i had previously (list of months) was automatically formatted as date-time. Changing it to text format fixed it. Not sure why this bug happened on 1.1.2020 but at least can be fixed

Paul,

I assume you've given feedback via Numbers > Provide Numbers Feedback.

Generally it's a good idea to avoid using values with - / and other special characters for matches like this anyway (in Numbers or Excel).

But it is hard to make the case for Numbers doing smart date recognition within functions.

Month names explicitly formatted as Text do seem to match correctly. But to be sure, I just use month numbers.

SG

The problem is there is only a 'Date & Time' data format, not separate 'Date' and 'Time' formats. So the interpretation that Numbers does is very helpful in some contexts, at the cost of having to be careful with SUMIF, etc.

SG

Hi Phaedon2,

Not sure it is a good idea to hijack thread started by projectpage.

This has nothing to do with updates to Pages, which of course is a separate application.

I don't think the functions in Numbers have stopped working. They haven't on my machine.

I suspect the problem has more to do with how a particular Numbers document is set up and how the cells are formatted before building a Pop-Up Menu.

If you want literal text values that work well in SUMIF and SUMIFS (even when the year changes) then format first as Text and build the Pop-Up Menu

If you want Numbers to guess the Date & Time when it sees a month name (which can work in SUMIF and SUMIFS until the year changes but depending how your document is set up won't find matches after the year changes) then leave the cells formatted as Automatic and build the Pop-Up Menu.

SG

This isn't about Numbers guessing. This is a glitch. Let's take my monthly expenses below, with columns for the month and category. I've highlighted October to show you below that the actual value is "10/1/2019." As you've suggested, this type of auto-formatting is the culprit. I'll show you why this isn't the case.

Here is the SUMIFS function that works in 2019 and fails in 2020.

Again. Works in 2019. Fails in 2020. One more time. Works in 2019. Fails in 2020.

How is this possible? "=October" should work for both years, or neither. The year was never qualified. Conversations about what the cell was previously formatted as are moot.

Even if Numbers does something like, turn my typing "October" into a pop-up menu into "10/1/19" - which, evidently it does - it does not follow that the SUMIFS value "=October" would work in one year, and then fail the next. Why in the world would it? You keep inferring this is a data entry error when it is not.