Apple Event: May 7th at 7 am PT

> Learn more

> Add to your calendar 

User profile for user: thegoddelusion
thegoddelusion Author
User level: Level 1
10 points

Sorting table breaks my count of unique values

Would love some help with this. I have looked on these forums and came across 2 ways of doing this but both lead to issues somehow.


I need to gather all the unique values (in this case names of individuals) that appear in a column.


The first method was to create an extra column with the below formula that produces a number starting with 1 the first time appears in the column.

IF($A2≠"",COUNTIF(B$1:B2,Person '2024-04-17')+ROW()÷10000,1000000)


I then entered the following formula in "People Unique A" column to get a list of all the names.

IF(SMALL(Table 1::D,ROW()−1)>2,"",INDEX(Person,MATCH(SMALL(Table 1::D,ROW()−1),Table 1::D,0)))


The issue here is that when I sort the first table, some of the cells in the extra column produce errors (second screenshot below).



The second method does not need that extra column and only uses IF(COUNTIF($B$1:$B1,Person)=0,Person,"") in the cells of the "People Unique B" column.


The problem this time is that it produces errors in the "People Unique B" column if I have more rows in that second table than in the first table. It also produces a 0 the first time it encounters an empty cell in the "Person" column of table 1 (as shown in the first screenshot).


Also, both methods make it impossible to sort the second table, but this is not really an issue for me right now.




iMac 27″

Posted on Apr 17, 2024 12:04 PM

Reply
Question marked as Best reply
User profile for user: Badunit
Badunit
User level: Level 7
25,988 points

Posted on Apr 17, 2024 4:50 PM

First an alternate method of getting a list of unique names from a list, then a rant about bugs, and then a workaround for the problem you found with COUNTIF. Actually, I'll start by mentioning a pivot table can make a list of the unique names but it requires manual refreshing if new names are added or names are deleted.




Formulas in the righthand table are:

A2 =IFERROR(TEXTBEFORE(B2,"~"),"")

fill down to complete the column


B2 =TEXTJOIN("~",1,Table 1::B)&"~"


B3 =SUBSTITUTE(B2,A2&"~","")

fill down to complete the column


Hide column B


The list will be in the order the names appear in the table on the left. You can sort the table on the left. Don't sort the table on the right.


-------


I will take this opportunity to get back on my soap box yet again and complain about this longstanding bug with COUNTIF that Apple doesn't seem to care enough about to fix. It cropped up somewhere around version 10, which was around June/July 2020, and was reported to Apple sometime after that. It has been reported to them at least a few more times since then but, while they keep adding features to Numbers, they don't seem to care much about fixing computation and other bugs, of which this is only one of a long list. Numerous new versions and upgrades have come out and they continue to have this bug and many others. I used to report bugs to Apple and used to track to see when they got fixed but they quit fixing them so I stopped wasting my time. If you feel like it, please report it to them again using the menu item Numbers->Provide Numbers Feedback. But don't hold your breath waiting for a fix.


-----

One way to get around the bug you ran into is to not use ranges like B$1:B2 in the COUNTIF. Instead, use OFFSET to create the range. While we're at it, in your table it should start as B$2:B2 (B1 not included). If someone's name (or a word in the list) was "person", you would have a miscount if cell B1 is included. Yes, highly unlikely someone will have that name but you can see the problem.


For row 2, B$2:B2 can be created as

OFFSET(B$1,1,0,ROW()-1,1)

The table needs row 1 to be a header row to anchor B$1.


Full formula in B2 is =COUNTIF(OFFSET(B$1,1,0,ROW()−1,1),B2)

fill down to the remaining rows

View in context

Similar questions

5 replies
Question marked as Best reply
User profile for user: Badunit
Badunit
User level: Level 7
25,988 points

Apr 17, 2024 4:50 PM in response to thegoddelusion

First an alternate method of getting a list of unique names from a list, then a rant about bugs, and then a workaround for the problem you found with COUNTIF. Actually, I'll start by mentioning a pivot table can make a list of the unique names but it requires manual refreshing if new names are added or names are deleted.




Formulas in the righthand table are:

A2 =IFERROR(TEXTBEFORE(B2,"~"),"")

fill down to complete the column


B2 =TEXTJOIN("~",1,Table 1::B)&"~"


B3 =SUBSTITUTE(B2,A2&"~","")

fill down to complete the column


Hide column B


The list will be in the order the names appear in the table on the left. You can sort the table on the left. Don't sort the table on the right.


-------


I will take this opportunity to get back on my soap box yet again and complain about this longstanding bug with COUNTIF that Apple doesn't seem to care enough about to fix. It cropped up somewhere around version 10, which was around June/July 2020, and was reported to Apple sometime after that. It has been reported to them at least a few more times since then but, while they keep adding features to Numbers, they don't seem to care much about fixing computation and other bugs, of which this is only one of a long list. Numerous new versions and upgrades have come out and they continue to have this bug and many others. I used to report bugs to Apple and used to track to see when they got fixed but they quit fixing them so I stopped wasting my time. If you feel like it, please report it to them again using the menu item Numbers->Provide Numbers Feedback. But don't hold your breath waiting for a fix.


-----

One way to get around the bug you ran into is to not use ranges like B$1:B2 in the COUNTIF. Instead, use OFFSET to create the range. While we're at it, in your table it should start as B$2:B2 (B1 not included). If someone's name (or a word in the list) was "person", you would have a miscount if cell B1 is included. Yes, highly unlikely someone will have that name but you can see the problem.


For row 2, B$2:B2 can be created as

OFFSET(B$1,1,0,ROW()-1,1)

The table needs row 1 to be a header row to anchor B$1.


Full formula in B2 is =COUNTIF(OFFSET(B$1,1,0,ROW()−1,1),B2)

fill down to the remaining rows

Reply
User profile for user: thegoddelusion
thegoddelusion Author
User level: Level 1
10 points

Apr 17, 2024 5:34 PM in response to Badunit

Thanks for the reply, again!


Wow, I didn't think Apple would allow bugs to linger around like that. Disappointing. I'll report it as you suggested.


For the workaround, I'm struggling to make it work. It produces error symbols (that say This formula can’t reference its own cell, or depend on another formula that references this cell). You can see below what I have entered in column B of the second table.


Reply
User profile for user: thegoddelusion
thegoddelusion Author
User level: Level 1
10 points

Apr 18, 2024 4:19 PM in response to Badunit

I submitted a bug report concerning the COUNTIF. They got back to me asking for more info. I'm not sure how to best describe the issue though.


Hello,

We’re looking in to the issue reported in your feedback submission for iWork (Keynote, Pages, and Numbers). We’d like to have a better understanding about what happened, but will need more information. Is it okay for us to send a follow-up email with detailed instructions?

Thank you,
The iWork Team


Reply

Sorting table breaks my count of unique values

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