OK, so I know how to count unique values of a particular column. However what if i had once column contain systems and another with unqiue organisations
say systems in column a and organisations in B
What i want to do is know how many unique organisations are using say system A?
you are showing Excel version 2010 - is that still the version you are using
a sample sheet may have helped here
latest version you could use UNIQUE & FILTER
=UNIQUE(FILTER(A1:B21,A1:A21=1))
I have used 1 hardcoded - but the system could be a cell so it changes as you change cell
Maybe a pivot table - would work in 2010 - i'll look if you are still using 2010
EDIT _ I have added a pivot table - with a page filter
Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.
A sample sheet would help here
Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Here are the instructions, found at the top of the page again
Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
Last edited by etaf; 11-01-2022 at 06:24 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thank you for your help, please find attached. So say i want to know how many unique orgs sell bananas. Hope this helps
what version of excel
MS 365 version 2209
can you update the profile , to show the correct excel version, as it makes a lot of difference as to which functions are included
the word banana has a space at the end , which i have removed - but could be included and the entry in I2 would need to have a space OR include the text
=UNIQUE(FILTER(A2:B21,B2:B21=I2))
I2 has banana in
=UNIQUE(FILTER(A2:B21,B2:B21="banana"))
OR
with a space - as the data has
=UNIQUE(FILTER(A2:B21,B2:B21="banana "))
One way:
=COUNTA(UNIQUE(FILTER(A2:A8,TRIM(B2:B8)="Banana")))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you all for your help.
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks