I have a list of identification numbers and locations.
I would like to count a number or text only once.
In my example attached I can see Texas 4 times, California 3 times and Jersey 1 times. I would like to create formula that counts each location only once so my total is 3 ( as I have only 3 distinct location: Texas, California and Jersey).
Thank you!
highlight A1:B9
Click on Data -- Sort & Filter -- Advanced
Click on Unique records only
In the Copy to: box list where you would like results
this will copy each individual unique item to the copy to range.
You can then use Counta function to determine how many unique values you have.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I have found the formula that works: =SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))
But I need to add a criteria which is another column with categories like P1, P2 etc. I need to see the unique entries for each category, can you help with updating the formula?
You will need to use COUNTIFS.
Then in G4 and filled across.Formula:
Please Login or Register to view this content.
Last edited by FlameRetired; 04-19-2018 at 07:44 PM.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks