Numbers dynamic COUNTIF

"The function does not copy down dynamically"

Interesting. From what I can tell, Numbers will copy down a formula if it detects the same formula in the last two rows of a column. The formula you are using is perhaps not quite the same in each row, the way Numbers sees it. The range over which it operates changes in size as you go down. Personally, I think it should fill down but, since it doesn't, that is my explanation why.

I tried Option-Arrow Down as Barry suggested but it did not work any differently for me with this kind of formula. I did not recreate his entire table, or yours, for testing; I used a smaller example table. I am not sure what he is doing vs what I am doing. I hope he enlightens me because the alternative is not as easy.

If you cannot get Barry's solution to work, another solution would be to make the formula look the same to Numbers from row to row. Instead of using the range $D$1:D2, substitute that part with OFFSET(D$1,0,0,ROW()) .

Hi Claire,

This works well until I add a new row on the Master sheet to create a new record, the function does not copy down dynamically

If you click in the last row that contains formulas and Add Row Below, that will copy the formulas down.

A suggestion. Make the last row (Total Children) a Footer row. Delete blank rows above it. Then when you click in the last body row, press return to add a new body row. Do the formulas copy down?

Or if I sort the Master sheet in any way, say to make a collection list by class number the Register returns blank cells.

Copy those cells and Menu > Edit > Paste Formula Results into a new table. Sort that table.

Regards,

Ian.

Hi Claire,

Here's a skeleton of your table, with only columns A, B and the columns involved in your formula filled in.

The core part of he formula is the same as yours, but rather than IFERROR, I've wrapped it in an IF statement that tests for content in column B and returns "" if it finds none. IFERROR works too, but works too well, trapping any error that occurs without giving notice of the error.

I didn't try adding a row by dragging the row control down, but did do so by clicking on any cell in the last row containing data (yellow filled cell in the image), then pressing option-down arrow. This action adds a row (or column) in the direction from the selected cell chosen by your choice of arrow key.

The formula was carried down into the new row (and remained in the row bumped down), as evidenced by the changes in column N on entering "t" into the new row and bumped row of column D:

Regards,

Barry

Claire, you wrote: "The function does not copy down dynamically".

This is indeed the case in the original Table 1, where the functions in columns E and F do not copy down from row 9 on, but the function in column G does copy down from row 9 on.

Table 1:

Then I copied the Table 1 to another sheet.

In the copied Table 1-1, all functions doe copy down when adding rows from row 9 on:

This looks like a (minor?) bug to me. At least inconsistent behaviour.

Paul.

Hi Claire,

My apologies. I tested your question with a very simple formula and it did fill down. I retested with your COUNTIF formula and it didn't fill down.

I was hoping for the collection registers to automatically update using a formula for user ease.

I am not sure what you are trying to do. More information please!

Regards,

Ian.

Hi Claire,

Thank you for this option, however I was hoping for the collection registers to automatically update using a formula for user ease.

Please start a new discussion for this question, or it will become messy if we deal with both questions at once.

What is your aim?

Regards,

Ian.

Hi Badunit,

Ditto. I can't get Barry's formula to automatically fill down with option down-arrow or return.

B10 is blank.

Regards,

Ian.

I confirm that here, too. Copy/paste of the entire table and that table will fill down. Copy/paste of the rows into a different existing table, it does not fill down.

Badunit wrote:

"I tried Option-Arrow Down as Barry suggested but it did not work any differently for me with this kind of formula. I did not recreate his entire table, or yours, for testing; I used a smaller example table. I am not sure what he is doing vs what I am doing. I hope he enlightens me because the alternative is not as easy."

Hi **,

My test table construction was as described. One thing I was probably doing differently from you and Yellowbox could be working in Numbers 3.6.2. A second might be that there was at least one row containing the formula below the row I selected before pressing option-down arrow, meaning the inserted row was between two rows containing the formula.

Regards,

Barry