SUMIFS is no longer working for me

Question

SUMIFS is no longer working for me

(screen shot below might explain this better than the text...)

I'm used to using SUMIFS to sum the values in a separate table & often use {date_column},">"&{date} as a condition to sum values where the date in the other table is greater than a date in this table. I've used this for years, no problem.

A couple of weeks ago & deleted a heap of rows in a table by mistake & hit undo repeatedly to restore them. All good, but the above expression now no longer works in that spreadsheet or any new ones I create ... but still works fine in old spreadsheets.

In addition to which if I simply say {date_column},{date} (i.e. that the date in the other table has to be the same as the date in this table) it works fine. So it seems to me it doesn't like me using '&' to concatenate '>' and {date}

I've looked for a setting that might disable the ability to concatenate using '&' but I can't see anything. Why does this work in old spreadsheets, but not new ones?

Help!

Posted on Mar 15, 2024 11:12 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,732 points

Mar 22, 2024 7:01 AM in response to Music on vinyl

Are you in a region other than the US where the standard format is DD/MM/YY? The date format in your spreadsheet is not a standard one for US (at least not in version 13.1 or below) but is for other regions such as UK.

I recreated the spreadsheet from your screenshot in the UK region, where that date format is a standard one. It worked properly and gave all correct answers. I changed the spreadsheet's region to US where that is not a standard format and all the answers went to 0. Maybe it is related to the region of your computer or of the spreadsheets themselves?

Did you try using DATEVALUE?

=SUMIFS(New Table::B,New Table::A,">"&DATEVALUE($A2),New Table::A,"<="&DATEVALUE($A3))

This gave correct answers every time.

Reply

Answer 2

Badunit

Level 7

30,732 points

Mar 16, 2024 5:45 AM in response to Music on vinyl

I am able to reproduce the problem in 13.1 when using a custom date format like in your table. If A2 and A3 are formatted with a built-in date&time format it works correctly. When formatted with your custom format it does not. Why it does not work and, more curiously, why it works in some of your spreadsheets and not in others, I don't know. Probably related to the problem is if you format your custom dates as text then try to format them back to date&time, they remain as text. I think they are so far afield of what Numbers recognizes as a date that it is screwing things up. It shouldn't be, but I think it is. But SUMIFS isn't simply switching into comparing text-to-text vs date-to-date, it is getting into some kind of mixed up mode where the "=" and other comparators make it fail.

I have a few workarounds for this bug:

Use a built-in date format for all dates or
Use DATEVALUE(A2) and DATEVALUE(A3) in your formula to force them to be dates
Use DATEVALUE(A2) and DATEVALUE(A3) in two other cells and reference those cells in your formula

I will report this bug and reference this thread as the explanation. Let me know if it ever gets fixed. I'm stuck on Numbers 13.1 unless I buy new computers to replace the perfectly capable ones I have now just to be able to run the latest version of Numbers on them.

Reply

Answer 3

Mar 22, 2024 9:35 AM in response to Badunit

Yes - I'm in the UK

Now that is impressive ...

Firstly that it's been a while since you first tried to help me, it took me ages to respond, and now you've come back almost immediately with further help - that's real dedicated to help someone you don't even know. So thank you for that, very sincerely

2.

Secondly - YES!! It works if I use DATEVALUE ... so that's brilliant; if nothing else I have my values back. And it rather implies that I've somehow messed up the Region on my spreadsheets ...

3.

If I check the File > Advanced > Language & Region of a spreadsheet that's OK, it's set to "English"

If I check the same on a spreadsheet that doesn't work (until I use your DATEVALUE tip) it's set to "System - English"... so there's a slight difference

Also, the menu item File > Advanced > Change File Type is greyed out for the spreadsheet that works.

It's set to "Single File" for the spreadsheets that need your DATEVALUE tip.

I'm not sure this is relevant ..

4.

More experimenting ...

If I then create a new spreadsheet & let it default to Region: English, it has the issue.

Changing it to a different Region (US) doesn't fix sheet1 , but if I create a new sheet (sheet2) in the same spreadsheet, it *is* fixed.

If I create a new table on sheet2 summing the values on sheet1, that works, too. So that's a US-formatted table summarising a UK-formatted table ...

If I use Option-File>New to create a new spreadsheet & set the region: English (United States) (and use US format dates), it doesn't have the issue ...

If I then change its value to UK it's still ok ... so I can potentially avoid this issue in future if I simply create every new spreadsheet as Region:US and immediately change it to UK (still a nuisance, but..)

So I've not yet figured out a way to fix a 'broken' spreadsheet, but I have a couple of workarounds.

I'm tempted to delete & reinstall Numbers, in case it's somehow related to inheriting the System Region

Reply

Answer 4

Badunit

Level 7

30,732 points

Mar 22, 2024 10:59 AM in response to Music on vinyl

System-English should use your system settings region, whatever it might be. UK I assume.

If it says "English" on the first line, that is the language but not the region. The second field will be the region of the spreadsheet.

The file type is something different. No need to mess with that.

Other than trying every combination of what if I do this, what if I do that, I'm not sure what to tell you right now. Maybe the screenshots below will shed some light on it, or eliminate some possibilities. The first is with it set to United States. Look at the format of the date. That is a standard format for Numbers in the US. Second is when set to UK. The date is a standard format for Numbers in the UK. When you change the region to UK, does the date look like the UK one?

Reply

Answer 5

Mar 22, 2024 5:46 AM in response to Badunit

Hi Badunit - thanks for your reply & apologies for the delay; I've replied a couple of times but for some reason the replies fail to post - quite frustrating.

Just for info, I'm on a 2020 M1 MacBook Air, running Sonoma 14.4, & Numbers 13.2. So the latest software on recent hardware.

Unfortunately, I'm using standard date data formats, not custom date formats, so that's not the problem. I can change date formats to other standard formats & it doesn't fix the broken spreadsheets, and both the working & broken spreadsheets are using the same formats. So, while I'd love it to be as simple as non standard date formats ... that's not it :-(

Reply

Answer 6

Yellowbox

Level 7

21,951 points

Mar 16, 2024 6:45 AM in response to Badunit

Hi **,

A further thought that Apple might consider is that there could be times attached that are not the default midnight, that may make > and <= fail. I have not tested.

Regards,

Ian.

Reply

Answer 7

Yellowbox

Level 7

21,951 points

Mar 15, 2024 11:25 PM in response to Music on vinyl

Hi Music on Vinyl,

I can't reproduce that problem in Numbers 13.2 under macOS Ventura 13.3.1

Regards,

Ian.

Reply

Answer 8

Badunit

Level 7

30,732 points

Mar 22, 2024 11:12 AM in response to Badunit

Is modifying the formula too difficult to do as a fix for the messed up spreadsheets. It's just the one formula, right, then copy/pasted or filled down the column?

Reply

SUMIFS is no longer working for me

Similar questions