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

Numbers: Count and display duplicates in a category

This is an extract of my spreadsheet:


I would like a way to:

  • Display the number of entries per a category
  • Display if each entry in a category is unique to that category


E.g. So the spreadsheet should look like this:


(I have added conditional formatting to make the false entries stand out. Although "Monday" appears twice, it is still classed as unique, because it only appears once in a category).



I’ve actually asked a similar question like this in the past. I was kindly suggested to use these formula:


A) to count numbers of entries per a category

=COUNTIF(A, A2)


B) To display if each entry in a category is unique to that category

=COUNTIF(A, A2)=1


The formula suggested worked perfectly for the past example.

However, in this new example, formula B just lists everything is false:



Here is my spreadsheet:

https://wsi.li/dl/rXu787c3otRbLfWCc/

(This is only an extract, as I am unable to post the full spreadsheet online).


Can anyone give me any pointers as to why it’s not working this time? I know in this example, the category comes first. But I have switched the formula around for that, so it should work. Thanks!


PS I know I can use Numbers built in category filters, but for this task, it needs to be done via formula, so it can fit in with my workflow.

MacBook Pro 15″

Posted on Jul 7, 2023 6:54 AM

Reply
Question marked as Top-ranking reply

Posted on Jul 8, 2023 8:14 AM

You need to test just the count of values within a specific category, not within all categories. To do that you can use COUNTIFS, like this:


=COUNTIFS(A,A2,B,B2)=1



SG

1 reply
Sort By: 

Numbers: Count and display duplicates in a category

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.