Question description:
This user has given permission to use the problem statement for this
blog.
I have a spreadsheet that contains a list of names/IDs and I want to generate a formula that inserts into a cell the number of times a name or ID appears on that list. Either insert a column into that spreadsheet or transfer that info into a column on a different spreadsheet. Either way would be fine.
Solved by O. L. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
19/09/2018 - 01:56
Hello, I understand that you want to be able to count the number of times a name appears in a list, right?
User
19/09/2018 - 01:56
Yes
Excelchat Expert
19/09/2018 - 01:56
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
19/09/2018 - 01:56
I may have a few quick question to help me fully understand your requirement.
Excelchat Expert
19/09/2018 - 01:56
Are you able to share your file so we can work on it?
User
19/09/2018 - 01:57
One minute
User
19/09/2018 - 01:59
OK - I had to clean up a file so I could show it to you. How do want me to share with you?
Excelchat Expert
19/09/2018 - 01:59
Please click the clip icon next t this chat.
Excelchat Expert
19/09/2018 - 01:59
Let me know once you've uploaded it.
User
19/09/2018 - 02:01
I am on my work computer and the accompanying site is blocked. I see no clip.
Excelchat Expert
19/09/2018 - 02:01
Here's how it looks.
[Uploaded an Excel file]
Excelchat Expert
19/09/2018 - 02:02
It's the green paperclip icon on the right side of where you are typing a message.
Excelchat Expert
19/09/2018 - 02:02
If you still can't, it's okay, we can try solving this without the file.
User
19/09/2018 - 02:02
OK - I don't see a clip but I do see a plain green box - I will try to click on that
User
19/09/2018 - 02:04
I think I did it??? There is a red "1" there now
[Uploaded an Excel file]
Excelchat Expert
19/09/2018 - 02:04
Great! Thank you.
Excelchat Expert
19/09/2018 - 02:04
May I know which column do you want to count?
User
19/09/2018 - 02:05
So, this is an example. I want to be able to count how many times, for example Smith, shows up in column A
Excelchat Expert
19/09/2018 - 02:06
Okay, for this we can use a COUNTIFS() formula. We can use COUNTIF but I'd suggest using the COUNTIFS variant in case you want to add criteria in the future.
Excelchat Expert
19/09/2018 - 02:06
I'll be working on this locally, please give me a few minutes.
User
19/09/2018 - 02:06
Roger that
Excelchat Expert
19/09/2018 - 02:06
Paste this formula in E3.
Excelchat Expert
19/09/2018 - 02:06
=COUNTIFS(A:A,A3)
Excelchat Expert
19/09/2018 - 02:06
Then drag it downwards.
User
19/09/2018 - 02:07
Wow! You rock!!
Excelchat Expert
19/09/2018 - 02:08
Great, so basically the formula means that you want to count the number of times the value in A3 is found in column A.
Excelchat Expert
19/09/2018 - 02:08
When you drag it downwards, the A3 changes to A4 so the next formula is now counting the value in A4 etc.
Excelchat Expert
19/09/2018 - 02:08
Would there be anything else that I can help you with regards to the original question?
User
19/09/2018 - 02:09
Why is there an "s: at the end of the formula? WHat would happen if it was just =countiff?
Excelchat Expert
19/09/2018 - 02:09
They are basically the same but countif will only accept 1 criteria.
Excelchat Expert
19/09/2018 - 02:10
You can use COUNTIFS everytime you need COUNTIF but you can't use COUNTIF if you need COUNTIFS.
Excelchat Expert
19/09/2018 - 02:10
So might as well just remember 1 formula that does both.
User
19/09/2018 - 02:11
Good point. Thanks so much for the help. I know this one was simple but it is so hard to figure out what to put in the Google search box to get the answer that I need.
Excelchat Expert
19/09/2018 - 02:11
Yes, that was my problem when I was also starting it!
Excelchat Expert
19/09/2018 - 02:11
Nothing beats asking a real person so you can explain thoroughly what you want.
Excelchat Expert
19/09/2018 - 02:12
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
19/09/2018 - 02:12
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
19/09/2018 - 02:12
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
19/09/2018 - 02:12
You got it!
User
19/09/2018 - 02:12
How much is this service normally?
Excelchat Expert
19/09/2018 - 02:13
I'm afraid that I'll have to ask you to visit our homepage for that.
Excelchat Expert
19/09/2018 - 02:13
It varies so I might give you wrong information.
User
19/09/2018 - 02:13
OK - thanks again 5 star expert!
Excelchat Expert
19/09/2018 - 02:13
Thank you I'd appreciate that. It would help me a lot. Please do not forget to click the End Session button so you can rate me.
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.
