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

Question

Level 1

15 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

Answer 1

Barry

Level 9

59,861 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

Answer 2

muguy

Level 9

76,129 points

Feb 6, 2022 9:44 AM in response to RWforumID777

Not sure of the formula, but you don't need to include rows at all, just use the entire column. If you add/remove rows, the column remains. =countif(C,1) will count all instances of "1" in column "C."

Reply

Answer 3

Feb 6, 2022 9:33 AM in response to RWforumID777

Have you tried setting up a Siri voice command using shortcuts? I would think they would support the iWork apps but it could help with several repetitive tasks saving time. I don’t use it often so this is just a possible suggestion

Reply

Answer 4

RWforumID777 Author

Level 1

15 points

Feb 6, 2022 9:44 AM in response to DiagnosedCrazy-ItsOK

Good idea, but at the moment it likely would be more efficient to start with 1,000 rows and hide them. However your suggestion is definitely a creative solution that could be applied in a variety of ways. I've used shortcuts just for a few basic things on my iOS device but I have not used them at all on my Mac... yet...

Reply

Answer 5

RWforumID777 Author

Level 1

15 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

Answer 6

Feb 11, 2022 10:43 AM in response to RWforumID777

Have you tried highlighting a few of the bottom rows and pulling down to add several more rows at a time? In excel this is how it was needed to be done to pick up any variations in the formula or show it was just repeated. Unfortunately I’m redoing my system currently and haven’t installed numbers to test it out.

Reply

Answer 7

RWforumID777 Author

Level 1

15 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

Answer 8

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

Answer 9

RWforumID777 Author

Level 1

15 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

Answer 10

RWforumID777 Author

Level 1

15 points

Feb 14, 2022 9:47 AM in response to Barry

Hello Barry,

I tried your method doing a quick test on one of my tables and it appears to work perfectly. I am going to do some work on the tables during the next week, I've made a reminder to post an update here to update you or to ask other related questions if any come up. Thanks,

Rod

Reply

Answer 11

RWforumID777 Author

Level 1

15 points

Feb 22, 2022 8:31 AM in response to RWforumID777

Hello again Barry!

After using your method for the last week I'm confirming that it's the perfect solution. So very easy now that I'm aware of it, thank-you very much.

Reply

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

Similar questions