Excel - COLUMN Function Problem - Expert Solution

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

Hello, I have a spreadsheet with 3159 rows. One column consists of 9-digit numbers in each of the 3159 rows. I need the formula that can calculate how many of those numbers appear more than once.
Solved by D. H. in 56 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 08/08/2018 - 02:31
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
Excelchat Expert 08/08/2018 - 02:33
From what I understood in your post, you would want to know how many of the 9-digit numbers have appeared more than once in your file composing of 3159 rows. Is this correct? :)
Excelchat Expert 08/08/2018 - 02:34
Hello? :)
Excelchat Expert 08/08/2018 - 02:36
It would be great if you can share the file you're working on, so that we can directly insert the formula in there. However, we can as well work on a sample data in the document preview on the right. :)
Excelchat Expert 08/08/2018 - 02:37
I can see your avatar as present in the document preview. I hope you'd be able to respond the soonest possible time so I could help! :)
User 08/08/2018 - 02:38
sorry im here
Excelchat Expert 08/08/2018 - 02:38
Oh nice! Thanks for responding. Will you be able to share the file you're working on? :)
User 08/08/2018 - 02:39
i didn't realize you were answering my question. I thought I had to post it on your site once i changed the temp password
User 08/08/2018 - 02:39
yes i can share it
User 08/08/2018 - 02:39
how do i send it to you
Excelchat Expert 08/08/2018 - 02:39
That would be nice! You can upload your file by clicking on the clip icon to the right and selecting your file. :)
User 08/08/2018 - 02:40
[Uploaded an Excel file]
User 08/08/2018 - 02:40
did you receive it
Excelchat Expert 08/08/2018 - 02:40
Yes, opening now.
User 08/08/2018 - 02:40
it should already be open on the tab "LOS and TransDown Combined"
Excelchat Expert 08/08/2018 - 02:41
Okay I see. Is column C the one we need to count on?
User 08/08/2018 - 02:41
No, column A
Excelchat Expert 08/08/2018 - 02:42
Oh I see, noted on that. So just to clarify on what you want to happen, you would want to count how many values in column A have appeared more than once, correct? :)
User 08/08/2018 - 02:42
User 08/08/2018 - 02:43
I need the forumla not the just the answer
User 08/08/2018 - 02:43
or will u use a pivot table
Excelchat Expert 08/08/2018 - 02:43
Okay noted on that. For this one, we will need to use a helper column to aide us in your main formula.
Excelchat Expert 08/08/2018 - 02:44
Using pivot will allow us to know how many times the visit numbers have appeared. We can as well make use of this if you wish. However, using a helper column would suffice.
User 08/08/2018 - 02:44
we can use the helper column
Excelchat Expert 08/08/2018 - 02:45
Noted on that. Let me work on it for a while and update you the soonest!
User 08/08/2018 - 02:45
User 08/08/2018 - 02:45
can i leave my desk for five minutes?
Excelchat Expert 08/08/2018 - 02:46
Oh, I will need you to extend our session once it reaches 3 minutes! :)
User 08/08/2018 - 02:46
okay no prob. i'll stay here
Excelchat Expert 08/08/2018 - 02:46
Okay, thanks!
Excelchat Expert 08/08/2018 - 02:48
Where do we need the total count of values that appears more than once?
User 08/08/2018 - 02:48
under the column in question or in column M
Excelchat Expert 08/08/2018 - 02:49
Okay, let me just put it under column A then. I'm almost done. I'll be able to upload the file once done.
User 08/08/2018 - 02:49
Excelchat Expert 08/08/2018 - 02:50
Okay, please see attached for your review!
[Uploaded an Excel file]
Excelchat Expert 08/08/2018 - 02:52
I inserted a a helper column in column B, which has the following formula: =IF(COUNTIF($A$2:A3,A3)=2,"Yes","") This returns "Yes" once it detects that the value aligned to it has already appeared once above. However, if the COUNTIF detects that the value aligned to it appeared already twice, it won't return "Yes". This prevents us from having two "Yes" values for the same number. Thus, as long as the number has appeared more than once, it would return a Yes only ONCE even if it appeared 50 times.
User 08/08/2018 - 02:53
some entries have more than two
User 08/08/2018 - 02:53
more than two occurrences
Excelchat Expert 08/08/2018 - 02:53
If an entry appears more than two, it should be counted once only, correct?
User 08/08/2018 - 02:53
Excelchat Expert 08/08/2018 - 02:54
Okay, that's what the formula above does. So basically, even if it appeared 50 times, that value will only have one "Yes" in the whole column B.
User 08/08/2018 - 02:55
i know it's something simple but can you tell me the formula for counting the yes's in the helper column
Excelchat Expert 08/08/2018 - 02:55
In cell A3161, we have the formula =COUNTIF(B2:B3159,"Yes"). This counts all the "Yes" values in column B. Take note that a number that appears twice, thrice, or 50 times in column A is only counted ONCE.
Excelchat Expert 08/08/2018 - 02:56
The Yes's are counted in cell A3161. :) It uses the COUNTIF function. This function has the following syntax: =COUNTIF(array , criteria). So our criteria is "Yes" and our array is B2:B3159. So in this array, COUNTIF will count the times the "Yes" value has appeared. :)
User 08/08/2018 - 02:56
i'm so sorry. i forgot to filter the Request Type column on Internal Transfer
Excelchat Expert 08/08/2018 - 02:57
It's alright. I already unfiltered it and applied our formula from row 1 to row 3159.
Excelchat Expert 08/08/2018 - 02:57
Sorry, starting row 2.
User 08/08/2018 - 02:58
what the total?
User 08/08/2018 - 02:58
of internal transfer >1 entries
Excelchat Expert 08/08/2018 - 02:59
Oh sorry, do you mean to say you want to only count the numbers that appear more than once only for Internal transfer?
User 08/08/2018 - 02:59
User 08/08/2018 - 02:59
Excelchat Expert 08/08/2018 - 02:59
Okay, I see. Let me reconfigure our formula then.
User 08/08/2018 - 03:00
cant we filter out everything except Internal Transfer and use the formula you used already?
Excelchat Expert 08/08/2018 - 03:01
Already done! Please see attached for the updated formula.
[Uploaded an Excel file]
Excelchat Expert 08/08/2018 - 03:02
Basically, we filtered only Internal transfer, and our formula changed to =IF(COUNTIFS($A$2:A2,A2,$C$2:C2,"Internal Transfer")=2,"Yes","")
Excelchat Expert 08/08/2018 - 03:02
This ensures that it will only count the number when the request type is "Internal transfer"
User 08/08/2018 - 03:02
something is wrong, i'm afraid
Excelchat Expert 08/08/2018 - 03:03
Sorry, what may be wrong?
Excelchat Expert 08/08/2018 - 03:03
Were you able to check the latest updated file I attached? :)
User 08/08/2018 - 03:03
it you will notice I used conditional formatting to highlight duplicates. And I counted the red highlighted entries manually. There are 113
User 08/08/2018 - 03:03
and someone used python and got 113
User 08/08/2018 - 03:04
yes, i have the latest greatest open. the count is 120
Excelchat Expert 08/08/2018 - 03:04
Okay I see. Let me recheck the formulas then.
Excelchat Expert 08/08/2018 - 03:08
I do have an error with the formula. Thanks for pointing that out! Let me revise it quickly and upload it to you.
User 08/08/2018 - 03:08
Excelchat Expert 08/08/2018 - 03:17
Already done! Please see attached for your review.
[Uploaded an Excel file]
User 08/08/2018 - 03:17
User 08/08/2018 - 03:17
User 08/08/2018 - 03:18
the formula is much simpler
Excelchat Expert 08/08/2018 - 03:18
The second previous formula provided was modified to incorporate an AND function. This lets us still use the COUNTIFS function, but another criteria was added, which is that the cell in column C should have "Internal Transfer" as data. :)
Excelchat Expert 08/08/2018 - 03:18
Do you have any clarifications with the provided solution? :)
User 08/08/2018 - 03:18
so COUNTIF finds dupes??
User 08/08/2018 - 03:19
or did you do something first before using that formula
Excelchat Expert 08/08/2018 - 03:19
You mean the COUNTIF in cell A3164 or the COUNTIFS in column B? :)
User 08/08/2018 - 03:20
OMG!! what a hefty formula you had to use!!!
Excelchat Expert 08/08/2018 - 03:21
The COUNTIF in cell A3164 counts the number of cells in column B that has a "Yes" value. The formula in column B incorporates the COUNTIFS function to determine if a value in column A is a duplicate AND that the value in column C is "Internal Transfer"
User 08/08/2018 - 03:22
you are smart!!!
Excelchat Expert 08/08/2018 - 03:23
It's a bit of a tricky calculation, but we needed to use multiple functions to return the correct "Yes" values. Basically, the formula in column B ensures that a "Yes" is returned IF the value has already appeared more than once and that the request type is "internal transfer"
Excelchat Expert 08/08/2018 - 03:23
Thanks for that! :)
User 08/08/2018 - 03:23
did you already explain how your arrived at the formula in column B?
Excelchat Expert 08/08/2018 - 03:24
Well, I had tests and retests until I arrived with the formula. :) I had to jumble actually the functions used until they're in the right place. And somehow, after seeing that it's working correctly, it already made sense when I try to follow how Excel calculates with the use of that formula.
Excelchat Expert 08/08/2018 - 03:25
Basically, the first thing I did was to clear out the conditions needed for a "Yes". I just had to translate this then into a formula. :)
User 08/08/2018 - 03:26
thank you very much. I'll have to study what you have taught me
Excelchat Expert 08/08/2018 - 03:27
Thanks! Yes, I hope you'd be able to follow the steps moving forward. :)
Excelchat Expert 08/08/2018 - 03:27
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
User 08/08/2018 - 03:27
I hope too
Excelchat Expert 08/08/2018 - 03:27
I'm sure you'll do! :)
User 08/08/2018 - 03:27
thanks again!
Excelchat Expert 08/08/2018 - 03:28
Hoping for your great feedback on this. Have a nice day! You can now press on the END SESSION on the upper right of your screen. :)
User 08/08/2018 - 03:28
Definitely was great help. Be happy to fill out the feedback form.
User 08/08/2018 - 03:28

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc