SUMIFS formula broken by latest update (14.4)

I use a rather complex spreadsheet to analyse weekly shopping orders, break things down by category, etc. It involves quite a bit of cross-referencing across individual sheets and tables, so the formulae are rather long. With the latest Numbers update (14.4), it seems that the SUMIFS function which I use in some places has been broken – but only in part.


I use a summary sheet for each year, 2023 to the current one. For each year, there are also sub-sheets that contain extra data that I may need to reference for the summary. In this case, the SUMIFS formula for the 2025 summary sheet/table works fine, but the exact same thing doesn't work on the 2024 summary sheet with its corresponding references anymore. It all worked fine before the update.


When I opened the file for the first time, there was a message "This sheet may look different", and "@" symbols had been added to formulas for some reason... it made no sense to me.


The formula I use for the main summary cell that others depend on is this:


SUMIFS('''25 Ocado'::Ocado::'w/o vouchers','''25 Ocado'::Ocado::A,">="&$A3,'''25 Ocado'::Ocado::A,"<"&$A4) + SUMIFS('''25 Other'::Other purchases::Total,'''25 Other'::Other purchases::A,">="&A3,'''25 Other'::Other purchases::A,"<"&A4)


To explain: The formula is to sum the total shop value ("w/o vouchers") from one sheet ("'25 Ocado"), of those within a given month. The limits of the date range are calculated by referencing cells in the same table, i.e. on or after 1 January, and before 1 February (this is represented in the formula by the ">="&$A3 (January) and "<"&$A4 (February) elements). To this are then added (the half after the +) values from another sheet, based on the same rationale.


I have attached a screenshot of the summary table that this formula is used in (in cells B3-B6 respectively), and from the sheet for 2025 where it calculates just fine.


However, the exact same thing for the summary of 2024 returns 0 everywhere, even though the component references seem correct:



When I click on the first item in the SUMIFS formula (i.e. the values to be summed), the "Value" section below displays the warning symbol saying "The formula needs 43 more rows to spill its results." I have no idea what that means.


The same thing happens on a subordinate sheet, which breaks down additional shopping into categories, but all return 0.00. This is the sheet that that the values for the second half of the above formula are added from (the left table).



The formula used in the right-hand table is similar to the one above, with added conditions (for March):

SUMIFS(Other purchases::$Total,Other purchases::$A,">="&$A4,Other purchases::$A,"<"&$A5,Other purchases::$Kind,"Alcohol")

The purpose of this formula is to sum up the value of purchases from the left-hand table, in a given timeframe (by month), by category. So the "Alcohol" category for March should read £49.98, sweets £158.62, and so on.


I have no idea what has happened, because it all used to work fine. It still does on the sheets for 2025, an 2023, but not 2024, even though the formulae were all copied and updated to reference the correct cells.

MacBook Pro 14″

Posted on Apr 4, 2025 3:58 AM

Reply
Question marked as ⚠️ Top-ranking reply

Posted on Apr 5, 2025 10:53 AM

This is probably related to Number's support for arrays vs. previous versions.


However, the formulas you post don't seem to suffer from this, but there may be other formulas in the intermediate cells that are tripping it up.


Without seeing more of the sheet, it's going to take some troubleshooting to walk back through the formulas to find where it's breaking.


I'd also suggest looking at a pivot table for your data, since (at first glance) this looks like you're trying to write formulas to summarize your data, which pivot tables can do with a couple of clicks.

6 replies
Sort By: 
Question marked as ⚠️ Top-ranking reply

Apr 5, 2025 10:53 AM in response to amarenesciri

This is probably related to Number's support for arrays vs. previous versions.


However, the formulas you post don't seem to suffer from this, but there may be other formulas in the intermediate cells that are tripping it up.


Without seeing more of the sheet, it's going to take some troubleshooting to walk back through the formulas to find where it's breaking.


I'd also suggest looking at a pivot table for your data, since (at first glance) this looks like you're trying to write formulas to summarize your data, which pivot tables can do with a couple of clicks.

Reply

Apr 16, 2025 5:45 AM in response to amarenesciri

amarenesciri wrote:

the SUMIFS formula for the 2025 summary sheet/table works fine, but the exact same thing doesn't work on the 2024 summary sheet with its corresponding references anymore. It all worked fine before the update.


I find Pivot Tables easier.


But if you're looking for clues why your formula works for 2025 but not for 2024 then you might look into what values you have in the cells with month names.


If you type a month name into a cell formatted as Automatic then Numbers will interpret what you enter as a Date & Time even though just the month name will be displayed in the cell. You can see that by selecting a cell with the month name and looking lower left to see what's actually in the cell.


A Date & Time includes a year, even when you have formatted the cell so you can't see it. You may have a year mismatch in 2024 that causes SUMIFS to return 0, whereas you don't have that mismatch problem for other years.


If you format a cell as Text and enter a month name then the resulting value in the cell will no longer display right-aligned by default as it would if Numbers interprets it as Date & Time. Its value will be the text of the name of the month that you entered.


SG

Reply

Apr 16, 2025 7:29 PM in response to PAgrampa

PAgrampa wrote:

the latest update broke the ability to select a cell, type the “=“ sight, the highlight any of the values above. The output is squirrelly.


I'm not able to reproduce the described problem on my machine. The update works well.


I do note that in your screenshot some of your month names are right-aligned (suggesting Date & Time value including the year) whereas other month names are not right-aligned (suggesting something else in the cell).


Cannot tell from your response if you tried selecting the cells and looking lower left to see what it actually there.


I also see you seem to have some blue triangles. Have you clicked on them to see what the message is? That might give clues about what is going on in your document.


SG

Reply

Apr 16, 2025 5:31 PM in response to amarenesciri

I’m not a sophisticated Numbers user, but the latest update broke the ability to select a cell, type the “=“ sight, the highlight any of the values above. The output is squirrelly. I use this all the time. I did speak with Apple Support on line, who will “look into it”. Hopefully a revision will be coming soon. I don’t understand how these things simply go untested before they are released on the user community.

Reply

SUMIFS formula broken by latest update (14.4)

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.