Finding duplicates in Excel

So we have this spreadsheet that has 23 rows.
We want to be able to count unique entries only.  Ones that are not duplicated.
All though this isn't hard to do manually for 23 rows a function was added to do it automatically

=SUM(IF(FREQUENCY($B3:$B23,$B3:$B23)>0,1))

It's not working correctly,  None of the 23 rows are duplicated. Yet it is saying 13 are unique, leaving 10 that are duplicates.

What did we do wrong here?

Thanks
John
John SheehySystem Security ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
This is an Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
Did you confirm it with Ctrl+Shift+Enter?
If yes and still you are getting incorrect output, please upload a sample workbook.
Saurabh Singh TeotiaCommented:
John,

A simpler refined version of this formula will be...

=SUM(1/COUNTIF(B3:B23,B3:B23))

You need to enter the above formula by ctrl+shift+enter rather then enter and it will do what you are looking for...

Enclosed workbook for your reference...

Saurabh...
Unique-Count.xlsx
John SheehySystem Security ManagerAuthor Commented:
I Get a #div/0 error but I think it's because the cells contain data like this:

P_10619
P_10620
9096
P_10630

When they are just numbers it works out just fine.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

John SheehySystem Security ManagerAuthor Commented:
Attached is the sample data.  Trying to get H26 to show the right number.  They are all unique so it should be displaying 19 as two have no data

This is the formula that was in there:
=SUM(IF(FREQUENCY($B3:$B23,$B3:$B23)>0,1))

John
John-Sheehy-Experts-Exchange.xlsx
Saurabh Singh TeotiaCommented:
John,

You got blank values that's why it's giving you an error..use this formula...

=SUMPRODUCT(($B$3:$B$23<>"")/COUNTIF($B$3:$B$23,$B$3:$B$23&""))

Enclosed your workbook..

Saurabh...
John-Sheehy-Experts-Exchange.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You are not getting the correct output with your formula because, Frequency works with Numbers as in Frequency data_array and bins_array both have to be numbers but your column B contains both numbers and texts.

So you will need to use Match within the data_array part of the frequency and row numbers within the bins_array part of the frequency formula. This is another way an frequency formula is used to count the unique text entries.

Therefore if you change your formula to the below one, it will give you the correct output...

=SUM(IF(FREQUENCY(IF($B3:$B23<>"",MATCH($B3:$B23,$B3:$B23,0)),ROW($B3:$B23)-ROW($B3)+1)>0,1))

Open in new window

I have placed this formula in D24.
Remember to confirm it with Ctrl+Shift+Enter.

Notice in the above formula, the Match will return the relative position of text or numbers in the array which is compared with the row number index generated by the part ROW($B3:$B23)-ROW($B3)+1.

For details refer to the attached sheet.
John-Sheehy-Experts-Exchange.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SheehySystem Security ManagerAuthor Commented:
So both your answers worked for my issue and both of you participated equally. So I am splitting the points.

John
John SheehySystem Security ManagerAuthor Commented:
Both answers worked for my issues.  They were precise and quick to respond.

John
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks John! Glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.