# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

So what Iâ€™m trying to achieve isto find value with highest frequency in a range so for instance thereâ€™s 46 time that Annaliseâ€™s name has been displayed. I need this display in an individual sheet for the person with their name the most repeated. I will need to filter this down by manager also so from there it will display EIN, STARTTIME, NCALLID, ouc. If we could do a screen sharing session that would be great so I can show you what needs to be done. Thank you!
Solved by M. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 31/01/2018 - 05:00
HI
Excelchat Expert 31/01/2018 - 05:00
User 31/01/2018 - 05:01
Can you see that?
Excelchat Expert 31/01/2018 - 05:01
are you there?
User 31/01/2018 - 05:01
Hi
Excelchat Expert 31/01/2018 - 05:01
yes
Excelchat Expert 31/01/2018 - 05:01
i can see
User 31/01/2018 - 05:01
Well first thanks for your help!
Excelchat Expert 31/01/2018 - 05:01
can you also put headers into this sheet
User 31/01/2018 - 05:02
yo REPLYREPLY ALLFORWARD Mark as unread Wheeler,B,Ben,HKU21E R Wed 31/01/2018 15:11 To: Wheeler,B,Ben,HKU21E R; Get more appsAction Items So what I’m trying to achieve isto find value with highest frequency in a range so for instance there’s 46 time that Annalise’s name has been displayed. I need this display in an individual sheet for the person with their name the most repeated. I will need to filter this down by manager also so from there it will display EIN, STARTTIME, NCALLID, ouc.
User 31/01/2018 - 05:02
Sorry
User 31/01/2018 - 05:03
So where it shows on sheet 2
User 31/01/2018 - 05:03
I'd like the information pulled from sheet1 and to be organised as it is in sheet 2
User 31/01/2018 - 05:03
if possible
User 31/01/2018 - 05:03
basically trying to figure out who's name appears the most
User 31/01/2018 - 05:04
so starting with June
User 31/01/2018 - 05:04
literally just want it if possible to show who's name is popping up the most
Excelchat Expert 31/01/2018 - 05:04
you can use pivot table on this data to count which gets repeated maximum number of times
User 31/01/2018 - 05:04
User 31/01/2018 - 05:05
but i need it as a formula
User 31/01/2018 - 05:05
not table
Excelchat Expert 31/01/2018 - 05:07
did you see the formula?
Excelchat Expert 31/01/2018 - 05:08
you can use countif formula
Excelchat Expert 31/01/2018 - 05:08
in countif first we specify the range and then criteria
Excelchat Expert 31/01/2018 - 05:08
are you there?
User 31/01/2018 - 05:08
Can it be viewed as one
User 31/01/2018 - 05:08
on on sheet 2
User 31/01/2018 - 05:09
So like a dropdown for the persons name
Excelchat Expert 31/01/2018 - 05:09
for example June smith
Excelchat Expert 31/01/2018 - 05:09
mentioning june smith in sheet 2
User 31/01/2018 - 05:09
yep
Excelchat Expert 31/01/2018 - 05:10
now use vlookup fucntion to get number of times it has come
Excelchat Expert 31/01/2018 - 05:10
did you get?
Excelchat Expert 31/01/2018 - 05:10
in vlookup we selection the name first and then select the second value against that name
User 31/01/2018 - 05:11
Anyway of doing it as a drop down, thats really good
Excelchat Expert 31/01/2018 - 05:11
i did not get you
Excelchat Expert 31/01/2018 - 05:12
can you explain me?
User 31/01/2018 - 05:12
so rather then typing the names
User 31/01/2018 - 05:12
you select and drop down
User 31/01/2018 - 05:12
and it shows the number
Excelchat Expert 31/01/2018 - 05:12
you can copy all the name and then remove duplicates from that list
Excelchat Expert 31/01/2018 - 05:12
i types here to just show you as an example
Excelchat Expert 31/01/2018 - 05:13
i wanted to explain concept of countif and vlookup which has been used here
Excelchat Expert 31/01/2018 - 05:13
countif function here only would have served your purpose
Excelchat Expert 31/01/2018 - 05:13
are you getting me?
User 31/01/2018 - 05:13
yeah i understand
Excelchat Expert 31/01/2018 - 05:13
cool
Excelchat Expert 31/01/2018 - 05:14
let me know if you need to understand anything else?
User 31/01/2018 - 05:14
so to clarify where it says Kyle Graves
User 31/01/2018 - 05:14
We can't have a drop down of names who's on sheet1 row g
Excelchat Expert 31/01/2018 - 05:15
by dropdown what do you mean..how do you want ot view it?
User 31/01/2018 - 05:15
like a drop down of names
Excelchat Expert 31/01/2018 - 05:15
to*
User 31/01/2018 - 05:15
so you don't need to manually type it
Excelchat Expert 31/01/2018 - 05:17
here you can copy and paste all the name and then remove duplicates from here
Excelchat Expert 31/01/2018 - 05:17
you don't have to type the name manually
User 31/01/2018 - 05:17
can you show
Excelchat Expert 31/01/2018 - 05:18
first select all the data and paste it whereever you require
Excelchat Expert 31/01/2018 - 05:18
do you get it?
Excelchat Expert 31/01/2018 - 05:19
then select that data and then click on data tab in your excel
Excelchat Expert 31/01/2018 - 05:19
here is an option of remove duplicates
Excelchat Expert 31/01/2018 - 05:19
did you get?
User 31/01/2018 - 05:20
2 secs
User 31/01/2018 - 05:20
can we do a screen sharing session
User 31/01/2018 - 05:20
by any change?
Excelchat Expert 31/01/2018 - 05:20
i dont have any tool to share the screen
User 31/01/2018 - 05:21
team viewer?
Excelchat Expert 31/01/2018 - 05:21
select the list of names and then select data tab on top
Excelchat Expert 31/01/2018 - 05:21
and then use remove duplicates
Excelchat Expert 31/01/2018 - 05:21
try these steps
Excelchat Expert 31/01/2018 - 05:21
you will be easily able to do it
Excelchat Expert 31/01/2018 - 05:23
do you get it?
User 31/01/2018 - 05:23
ok sorry 1 sec
User 31/01/2018 - 05:24
can we do the vlookup again
User 31/01/2018 - 05:24
User 31/01/2018 - 05:24
i tried to duplicate what you showed me but didn't work
Excelchat Expert 31/01/2018 - 05:24
ok
Excelchat Expert 31/01/2018 - 05:24
paste a fresh data in sheet 1
User 31/01/2018 - 05:25
I have
User 31/01/2018 - 05:25
we're just working with the rows in yellow
Excelchat Expert 31/01/2018 - 05:26
copied that is sheet 3
User 31/01/2018 - 05:26
erm
User 31/01/2018 - 05:26
but i need the correct row
User 31/01/2018 - 05:26
can we do it from sheet 1
User 31/01/2018 - 05:26
User 31/01/2018 - 05:26
and pull the data from AU
Excelchat Expert 31/01/2018 - 05:27
pulled au data in sheet 4
Excelchat Expert 31/01/2018 - 05:27
did you see?
User 31/01/2018 - 05:27
Yes ok
Excelchat Expert 31/01/2018 - 05:27
now apply countif on that
Excelchat Expert 31/01/2018 - 05:28
did you see
User 31/01/2018 - 05:28
yes
Excelchat Expert 31/01/2018 - 05:28
i applied countif
Excelchat Expert 31/01/2018 - 05:28
this gives the number of times name is there in the column A
User 31/01/2018 - 05:29
now the name thing
Excelchat Expert 31/01/2018 - 05:29
copy paste the names in different colum
Excelchat Expert 31/01/2018 - 05:29
in your excel on your desktop you will see that there is data tab
Excelchat Expert 31/01/2018 - 05:30
inside data tab there is remove duplicate
Excelchat Expert 31/01/2018 - 05:30
hit that
Excelchat Expert 31/01/2018 - 05:30
Excelchat Expert 31/01/2018 - 05:31
use vlookup on that
Excelchat Expert 31/01/2018 - 05:31
did you see?
Excelchat Expert 31/01/2018 - 05:32
kindly let me know if you don't understand anything?
Excelchat Expert 31/01/2018 - 05:35
are you there?
User 31/01/2018 - 05:37
hi
Excelchat Expert 31/01/2018 - 05:37
yes
Excelchat Expert 31/01/2018 - 05:37
did you get the explanation?
User 31/01/2018 - 05:37
sorry it froze
Excelchat Expert 31/01/2018 - 05:37
ohk
Excelchat Expert 31/01/2018 - 05:38
refer to sheet 4
User 31/01/2018 - 05:38
I don't understand why the vlookup isn't working on my PC
Excelchat Expert 31/01/2018 - 05:38
you need to freeze the cells
Excelchat Expert 31/01/2018 - 05:38
press f4 to free the cells when you select the range
Excelchat Expert 31/01/2018 - 05:38
see the formula i have used
User 31/01/2018 - 05:39
I'm really sorry, I'm 60 years old, not to good at excel
User 31/01/2018 - 05:39
Can we start again?
Excelchat Expert 31/01/2018 - 05:39
sure
User 31/01/2018 - 05:40
So sheet5
User 31/01/2018 - 05:40
is raw data
Excelchat Expert 31/01/2018 - 05:40
ok
User 31/01/2018 - 05:40
What I'm trying to achieve
User 31/01/2018 - 05:40
Where it says manager
User 31/01/2018 - 05:40
So starting from a different way now
User 31/01/2018 - 05:41
A list of the managers name in a drop down menu
User 31/01/2018 - 05:41
From there I need it to say the name of the advisor
User 31/01/2018 - 05:41
Who's name popped up the most
User 31/01/2018 - 05:41
with all the information like EIN OUC etc
User 31/01/2018 - 05:42
So not sure the best way to get this done
User 31/01/2018 - 05:42
I'll pay aswell
User 31/01/2018 - 05:42
but really need help
User 31/01/2018 - 05:42
So for instance ill show
User 31/01/2018 - 05:43
And for caller ID and time it shows a list for that advisor
User 31/01/2018 - 05:43
But it needs to be the person who's name popped up the most
Excelchat Expert 31/01/2018 - 05:45
so by countif you will know whose name popped the most
User 31/01/2018 - 05:45
Ok could you show me please
User 31/01/2018 - 05:45
So first off a drop down of the managers on the team
Excelchat Expert 31/01/2018 - 05:46
select the data in yellow and paste it in new sheet
Excelchat Expert 31/01/2018 - 05:46
remove the data which you dont require
User 31/01/2018 - 05:46
could we do it in this sheet please
User 31/01/2018 - 05:46
as i won't really understand later on what to do
Excelchat Expert 31/01/2018 - 05:46
i this sheet i am unable to use columns after AU
User 31/01/2018 - 05:47
ill minimise the other ones we don't need
User 31/01/2018 - 05:47
yeah thats fine, thats where it stops
Excelchat Expert 31/01/2018 - 05:47
in your desktop excel you will be able to do
Excelchat Expert 31/01/2018 - 05:48
this interface has limitations
User 31/01/2018 - 05:48
but the formulas will be the same
Excelchat Expert 31/01/2018 - 05:48
yes
User 31/01/2018 - 05:48
I'm not to smart at excel you see
User 31/01/2018 - 05:48
they won't be though
User 31/01/2018 - 05:48
can you minimise the tabes not in yellow?
User 31/01/2018 - 05:50
so i just want it to pull the data like i said, is it possible or do you need to do =COUNTIF(\$A:\$A,A3)
Excelchat Expert 31/01/2018 - 05:51
you should use this formula to know whose name popped up most
User 31/01/2018 - 05:51
Is it not possible then for what I'm asking
Excelchat Expert 31/01/2018 - 05:52
sophie murall is appearing only twice in the list
Excelchat Expert 31/01/2018 - 05:52
and this formula give 2 as result
User 31/01/2018 - 05:52
Thats fine
User 31/01/2018 - 05:53
I just want the person who pops up the most on the list
User 31/01/2018 - 05:53
to be displayed on another sheet
Excelchat Expert 31/01/2018 - 05:53
ok
User 31/01/2018 - 05:53
with the quantity of how many times they pop up
User 31/01/2018 - 05:53
If possible pleas
Excelchat Expert 31/01/2018 - 05:54
you can use index match
User 31/01/2018 - 05:55
Ok :) would you mind showing me please
User 31/01/2018 - 05:56
?
Excelchat Expert 31/01/2018 - 05:56
yes showing that only
User 31/01/2018 - 05:59
47 seconds :(

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.