Cumulative count in Numbers surviving sort and categories

There's some missing pieces here in terms of what you're trying to achieve, so I might be wrong in my assumptions... but I think I see what you're trying to do...

The thing with Categories is that Numbers will update references to maintain a sense of balance - such that a single, uncategorized range may be split once the data is categories, and Numbers will update the references to continue to point to the now-disparate data. That usually makes sense, but compounds what (I think) you're trying to do.

Either way, I took a run at this, and think I have a working solutions...

The formula I came up with is:

=COUNTIF(INDIRECT("$B$4:$B"&ROW()),INDIRECT("B"&ROW()))

This uses two INDIRECT() commands, the first one:

INDIRECT("$B$4:$B"&ROW())

works out the reference from cell $B$4 to the current row in column B, e.g. "$B$4:$B5"

The second INDIRECT()

INDIRECT("B"&ROW())

simply works out the cell in column B for the current row.

These are passed into COUNTIF() in the same way you're already using it. Now that you have dynamic references that take into account the current row, rather than specific row numbers, it should survive categorization and sorting.

COUNTIF has been broken for YEARS and Apple apparently doesn't think it is worth fixing. I have reported this sorting problem to Apple repeatedly over the years and posted it here a gazillion times, every time someone runs into it or there is yet another update that does not fix it. This bug is at least 6 years old, starting with version 10.0 (if not earlier) and has not been fixed in any of the numerous updates since then.

Sorting is also a problem with SUM when using ranges of the form C$2:C2. I suspect it applies to other functions, too, but I have no incentive to do any further digging because it looks like they are never going to fix it so why waste my time. It is possible their code is so messed up that it would take a huge rewrite to fix this problem so they're going to continue to sweep it under the rug.

> COUNTIF has been broken for YEARS and Apple apparently doesn't think it is worth fixing

I'm not entirely sure I agree with your assessment or, at least, think it's more nuanced than you make it seem... at least in this case (or maybe you're referring to some other bug that I'm not thinking about...)

In this case, the issue is that the rows in the sheet get munged when you apply categories... that's by definition... the categorization groups rows together based on the categories you define and so the rows are all going to get shuffled

The question is how should formulas react to that reorganization...?

Take this simple example:

The formulas in Table 3 all reference the top 5 rows (excluding headers) in Table 1::C via a reference to Table 1::C2:C6.

While you might think of this as 'the top 5 rows', internally it's stored via a pointer to those cells... subsequently, if those cells move around (e.g. via categories, sorting, inserts, etc.), the references can update to track where those cells are.

For example, if I categorize and sort Table 1, above, I might get something like:

Note how the references in Table 3 have updated to "Table 1::C5:C6 | C8:C10". That's because they are still pointing to the same cells they were before (the ones with the values 100, 200, 300, 400, and 500). The fact the table was categorized and sorted hasn't caused Numbers to lose track of the cells it was using in the SUM, COUNT, and COUNTIF() formulas.

Note that it's pointing to the same values, even though those are in a different order.

Now, if you're saying that it should continue to point to cells C2:C6, regardless of how those cells get shuffled in a sort, that's a whole different question. Maybe there is some argument to saying that should an option (similar to how $ is used to mark absolute references, even though these, too, get tracked/retained through a sort). In the meantime if you want the SUM() of the first 5 values in the column you should use CHOOSEROWS() instead

I haven't broken out Excel to see how they handle it over there, but from memory Excel doesn't have in-table categorization, so it may not apply.

Again, I may have missed the specific 'bug' you're referring to, in which case I apologize :)

IIRC, Numbers used to sort "correctly" until some refugees from Excel kicked up a fuss and Apple listened.