Numbers Formulas Suddenly selecting Headers and Footers

Question

Numbers Formulas Suddenly selecting Headers and Footers

I have a numbers spreadsheet that I have been using for a long time. I use the default Apple table style that has built in headers and footers, see picture below.

For years, this has worked great, as when I am making a formula I can select the column (A, B, etc.) and the formula will automatically exclude the header and footer of the table.

Today, I accessed the sheet using on iCloud drive via a web browser. Since then, when I select a column it now inlcudes the header and footer of the table. This is frustrating as it ruins formulas.

I've seen this post that is similar, but did not receive an answer that resolves my question.

Is there a way to solve this?

MacBook Pro 14″, macOS 14.6

Posted on Nov 18, 2024 7:46 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,216 points

Nov 19, 2024 1:23 AM in response to beaucoupdequestions

Most importantly here, if you give YEAR a range rather than a single value, it uses one cell from that range. If your formula is in row 2 of its table, YEAR will be the year from row 2 of 'Fake Deposit Info for Post', it will not be an array of the years of all the dates in the column, it will be a single value. Your formula will be an error with the message that the ranges must be the same size. But, to answer your question, it is highlighting all the cells in the column because any one of them is a valid input for the YEAR function, even the headers and footers, when using a "whole column" range.

For this particular problem you can use SUMIFS

=SUMIFS(Fake Deposit Info for Post::Amount,Fake Deposit Info for Post::A,">="&DATE($A2,1,1),Fake Deposit Info for Post::A,"<"&DATE($A2+1,1,1))

Reply