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″