Apple Intelligence is now available on iPhone, iPad, and Mac!

⏺ Join the discussion

⏺ Press release 

⏺ Learn more

⏺ Watch the video

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: RWforumID777
RWforumID777 Author
User level: Level 1
17 points

How to input a formula that will automatically include new rows added to the end of a table

I can't figure out how to create a formula that will apply to new rows added at the bottom of a table.


MY USE CASE:

I use 3 status codes for my work, simply the number 1, 2, or 3 to indicate the stage of various projects. I have 3 separate COUNTIF formulas that adds up how many "1" or "2" or "3" in column D. On each Sheet I have 20-50 Tables (one for each project) and I start with just 5 rows in each table and I add rows as the project moves along, often resulting in several hundred rows. It is not practical or efficient to manually drag and adjust the formula for so many tables that are constantly having rows added.


THE PROBLEM:

In Google Sheets I can create a COUNTIF or any other type of formula with any number of rows so I will often put 1,000 as the row number because none of my tables surpass 1,000. Then I can delete all the extra rows, leaving just 5, but the formula will still read "1,000" and it will include new rows when I insert them later, up to 1,000 rows as inputted in the formula. In Apple Numbers however, when I shorten a table by grabbing the handle and dragging it up or by deleting rows, the formula will adjust itself, reducing the row number to how many are actually in the table. Subsequently the formula will adjust ONLY if I insert a row somewhere in the middle of the table, but if I add a row at the end or grab the handle with my mouse and pull it down to add rows the formula will not include those new rows. Inserting rows in the middle of the table would be fine if I needed just one but sometimes I need 20 or 30 so it's more practical to grab the handle and drag it down, but then my COUNTIF formula will not automatically include the new rows.


SEMI-AWKWARD SOLUTION:

I can create a table with 1,000 rows, input my formula, and then hide the rows I don't immediately use.


BETTER FIX BUT I CAN'T FIND HOW TO IMPLEMENT IT - IF IT CAN BE DONE AT ALL:

How can I tell Apple Numbers to automatically include new rows added AT THE END of the table - so I can either 1) grab the handle with my mouse and drag down a few rows to add them in or 2) right-click click on the last row and choose "add row below" and have the COUNTIF formula automatically include new rows?


Can Numbers be reprogrammed by the Apple team so that users can input any row number in the formula and have that number retained even if the user deletes rows (as per the method available in Google Sheets)?


If there is no syntax or menu option for this please consider this post to be a feature request.


Thanks,

Rod

Posted on Feb 6, 2022 9:25 AM

Reply
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Posted on Feb 11, 2022 5:09 PM

Hi Rod,


Selecting a 'whole column' when building a formula should be done by clicking the reference tab for that column. Depending on the function being used, this will select ALL cells in the column OR will select ALL cells in the column EXCEPT those in Header rows and those in Footer rows.


Your formulas, using COUNTIF fit in the latter category.

In the table above, row 1 is a Header row.

Rows 21, 22 and 23 are Footer rows.


When writing the formula, I:

  • entered the three category numbers in cells B21, B22 and B33 respectively.
  • Clicked cell C21 (once) to select it.
  • pressed = to open the formula editor.
  • typed countif( to insert the COUNTIF function and its prompt lozenges into the editor.
  • Clicked the first lozenge to select it, then reference tab above column C to select all cells in the body part of column C, and set this argument to that selection.
  • Clicked the second lozenge to select it, then clicked cell B21 to set this argument to that cell.*
  • Clicked the green checkmark to confirm the formula and close the editor.
  • Dragged the Fill control (small yellow circle) down to fill the formula into cells C22 and C23.


  • *(Note) the formula shown in in C22


As rows are added to the table, they will be inserted above the first Footer row.

Formulas placed in any of the columns will be automatically added to the new rows (and incremented as needed).


Regards,

Barry


View in context

Similar questions

11 replies
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Feb 11, 2022 5:09 PM in response to RWforumID777

Hi Rod,


Selecting a 'whole column' when building a formula should be done by clicking the reference tab for that column. Depending on the function being used, this will select ALL cells in the column OR will select ALL cells in the column EXCEPT those in Header rows and those in Footer rows.


Your formulas, using COUNTIF fit in the latter category.

In the table above, row 1 is a Header row.

Rows 21, 22 and 23 are Footer rows.


When writing the formula, I:

  • entered the three category numbers in cells B21, B22 and B33 respectively.
  • Clicked cell C21 (once) to select it.
  • pressed = to open the formula editor.
  • typed countif( to insert the COUNTIF function and its prompt lozenges into the editor.
  • Clicked the first lozenge to select it, then reference tab above column C to select all cells in the body part of column C, and set this argument to that selection.
  • Clicked the second lozenge to select it, then clicked cell B21 to set this argument to that cell.*
  • Clicked the green checkmark to confirm the formula and close the editor.
  • Dragged the Fill control (small yellow circle) down to fill the formula into cells C22 and C23.


  • *(Note) the formula shown in in C22


As rows are added to the table, they will be inserted above the first Footer row.

Formulas placed in any of the columns will be automatically added to the new rows (and incremented as needed).


Regards,

Barry


Reply
User profile for user: RWforumID777
RWforumID777 Author
User level: Level 1
17 points

Feb 11, 2022 10:32 AM in response to muguy

I may have been a little too fast on the 'solved' button. Subsequently I realized that selecting the entire column will work only if there are no header rows. In most of my tables I have header rows so when using the entire column the formula returns an error. Any idea if there is another way to make this work? It seems the only option is to insert new rows because simply dragging the handle down (or adding a new row from the last one at the bottom) will add a row but it will not result in the formula including the new rows.

PS how to I remove the 'solved' button? I am logged in but it will not let me remove the 'solved' status.

Reply
User profile for user: RWforumID777
RWforumID777 Author
User level: Level 1
17 points

Feb 11, 2022 10:48 AM in response to DiagnosedCrazy-ItsOK

I think perhaps there is a misunderstanding. What I am referring to is having a formula that will total the entire column, for example, total of rows 3 up to 10. Then afterwards if I add a row I want the formula to total rows 3 up to 11. I believe what you are referring to is having all the formulas in row 10 automatically included in row 11 after adding a new row, and Numbers does that when dragging the handle down or when inserting a new row at the bottom; that feature works great. But I can not find a method to have a 'total' or 'sum' include ALL rows, even new ones, except for selecting the entire column. But then selecting the entire column produces errors if there are header rows with text or other formulas in them.

Reply
User profile for user: DiagnosedCrazy-ItsOK
DiagnosedCrazy-ItsOK
User level: Level 1
118 points

Feb 11, 2022 11:08 AM in response to RWforumID777

What about creating a formula in a completely separate column that just adds the sum of all the boxes in the column you keep needed to add additional data into. The formula would just be off to the side and include everything in the column. As a single separate cell you could create a little area with descriptions beside different formulas for anything else needed to be captured using formulas from the spreadsheet data? Would that work?

Reply
User profile for user: RWforumID777
RWforumID777 Author
User level: Level 1
17 points

Feb 11, 2022 11:19 AM in response to DiagnosedCrazy-ItsOK

I think what you mean is that instead of having the sum in the same column, to move it to a different column? If so that would work in the sense of using the entire column for the sum, but of course it makes the table a bit awkward. It's more intuitive to have the total for a column in the same column. If I put the 'total gain' in a new column it makes the table larger and I'm already tight on space, and if I put it in one of the columns to the left or the right I'll have the total gain number sitting above a column that has data such as 'quantity' or 'price' which is a bit odd. So your suggestion will help if I want or need the formula to work but it's not as ideal as being able to have the 'total' in the same column and in some manner have it automatically update if I add rows.

Thanks for your idea, I'll check this thread over the next week or so and if a better solution does not come along I'll implement your suggestion.

Reply

How to input a formula that will automatically include new rows added to the end of a table

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