You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

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

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

Counting unique values in a single column - and make it still work after sorting

Hello, I have a column for names, a column (using a checkbox) for "no longer participating", and a column for a school. I want to count the sum of unique school in the column. I am using the formula I found on an older support page:


=IF(COUNTIF(A$1:A1, A2)=0,1,"")


It works. It's important to note that the name column is sorted with the names of participating students in ascending order, and then in the same column, the non-participating students are listed in ascending order. (Some school cells are blank for both participating and non-participating students). See screenshot:



If I need to change the sorted order to ALL students, participating and non-participating (in ascending or descending order), the formula breaks - specifically where the non-participating students get moved up and sorted among all students - the "A$1" loses its reference. How do I protect against this? Someone once showed me, and I think it involved using a hidden column that numbers each row so that even if the name changes position, the row number is always constant - but I don't remember the full solution. Thanks in advance.



iMac (2017 – 2020)

Posted on Aug 4, 2024 8:53 PM

Reply
Question marked as Top-ranking reply
User profile for user: Badunit
Badunit
User level: Level 7
28,324 points

Posted on Aug 5, 2024 1:39 AM

First thing to do is go into the Numbers menu, select "Provide Numbers Feedback", and report this bug. The formula should sort correctly without creating reference errors. We first noticed this bug four years ago. Quite a few new versions have come out since but with this (and a long list of other bugs) has not been fixed. Any formula that uses a reference like A$1:A1 is subject to breaking in a sort.


Here are two ways to do it. Both require a new column (column D).


D2=IFERROR(1/COUNTIF(C,C),0)

Fill down to complete the column

=SUM(D) to get the result

or you might want to use =ROUND(SUM(D),0) to ensure that it adds up to an integer. Computer math can have very very small errors with non-integral numbers. Not all base 10 non-integer numbers are perfectly representable in base 2 (binary). Numbers tries to correct for it but this would be extra insurance.


D2 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()−1),C2)=1,1,0)

fill down to complete the column

=SUM(D)

No need to round to 0 decimals. It will be an integer.

View in context
2 replies
Question marked as Top-ranking reply
User profile for user: Badunit
Badunit
User level: Level 7
28,324 points

Aug 5, 2024 1:39 AM in response to Zombocom1023

First thing to do is go into the Numbers menu, select "Provide Numbers Feedback", and report this bug. The formula should sort correctly without creating reference errors. We first noticed this bug four years ago. Quite a few new versions have come out since but with this (and a long list of other bugs) has not been fixed. Any formula that uses a reference like A$1:A1 is subject to breaking in a sort.


Here are two ways to do it. Both require a new column (column D).


D2=IFERROR(1/COUNTIF(C,C),0)

Fill down to complete the column

=SUM(D) to get the result

or you might want to use =ROUND(SUM(D),0) to ensure that it adds up to an integer. Computer math can have very very small errors with non-integral numbers. Not all base 10 non-integer numbers are perfectly representable in base 2 (binary). Numbers tries to correct for it but this would be extra insurance.


D2 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()−1),C2)=1,1,0)

fill down to complete the column

=SUM(D)

No need to round to 0 decimals. It will be an integer.

Reply

Counting unique values in a single column - and make it still work after sorting

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