I have a table with the following columns:
Column
- Column A - Country
- Column B - Office Type
I need to create a formula that returns a list of unique values from Column A, if the value in Column B = 'Office'. So in other words, rather than just tell me how many countries appear in Column A (many of which will be the same), I want to number of distinct countries we have offices in.
Any help much appreciated!
Thanks
Your title and description are at odds - what do you want? A count or a list?
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.
For a count:
=SUMPRODUCT((1/(COUNTIF($A$1:$A$1000,$A$1:$A$1000))*($B$1:$B$1000="Office")))
Hi there.
A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.
Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thank you - and sorry if my title / post was confusing.
I want a count of all of the distinct values in 'Country' column, if we were to filter the 'Office Type' column to 'Office'.
So I guess it would be done in two stages: What is the count of values in Column A when Column B = 'Office', and then what is the number of unique values in Column A.
I've tried your suggested formula but it bring up the wrong value (it should bring up 43, but it produces 68).
Have included the worksheet if that makes it easier?
Please try
=COUNT(1/FREQUENCY(IF(tbREMR7[Asset Type]="office",MATCH(tbREMR7[Country],tbREMR7[Country],)),ROW(tbREMR7)-ROW(tbREMR7[#Headers])))
Ctrl+Shift+Enter
or normal enter but slower with more data
=SUMPRODUCT((tbREMR7[Asset Type]="office")/COUNTIFS(tbREMR7[Country],tbREMR7[Country],tbREMR7[Asset Type],tbREMR7[Asset Type]))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks