Hi there,
I have 2 columns,
personsname and country , what im want to do is show the highest frequency of country per person in a range.
eg
question.PNG
(The above image is just an example of what im trying to achieve)
I've tried a combination of countif and unique but to no avail.
Coudl somebody point me in the right direction?
Thankyou for your time
Last edited by retroisbest; 10-23-2017 at 09:52 AM.
Hello and welcome to the forum.
With your names in A2:A9, countries in B2:B9, John Smith in D2, and Gill Smith in D3, try this in E2:
=INDEX(B$2:B$9,MODE(IF(A$2:A$9=D2,MATCH(B$2:B$9,B$2:B$9,0)))) Ctrl Shift Enter
Thankyou very much worked a treat!Originally Posted by 63falcondude
You're welcome. Thanks for the rep!
No problem its well deserved!
One little question if a person only exists once it errors out the result because mode is trying to match on more than one result is there anyway of still showing the country if there is only a single occurrence? (MODE cannot produce a result. No values occur more than once)
Don't worry if not im more than happy with this result
Sure. If there is no mode, you can use a regular matching formula to return the first match. Something like this:
=IFERROR(INDEX(B$2:B$9,MODE(IF(A$2:A$9=D2,MATCH(B$2:B$9,B$2:B$9,0)))),INDEX(B$2:B$9,MATCH(D2,A$2:A$9,0)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks