Sql Server Count if


I have an EXCEL 2010 spreadsheet that contains a column of ID numbers. There are over 300,000 rows; there are duplicates. I have a formula in a helper cell that count the occurances of an within the  range--if the count is 1, then the helper cell value is 1, if not 1, then the helper cell value is zero( =IF(COUNTIFS(D$1:D16019,D16019)=1,1,0).  Then I can count the helper cells with the value  of 1.

The problem is that this crashes excel once the row count gets over 200K.  Can this be done  with a simple  SQL  query?

Wm Allen SmithAsked:
Who is Participating?
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.

Swapnil NirmalManager, Audit AnalyticsCommented:
Use the following sql que

Count ID
From MyTable
Where ID = [Current ID]

Open in new window

If the result is more than 1 then its repeated
Wm Allen SmithAuthor Commented:
Swapnil Nirmal,

Thank you for your quick response. Your solution will not work for me. First, there are over 18000 unique ID's in the list of over 300000 rows. In addition, I just need the helper cell value adjacent to the id cell to be either 1 or 0 so that I can filter  the unique ID's.

Swapnil NirmalManager, Audit AnalyticsCommented:
Count IF on large range for multiple cells will always lead to crash of excel. Instead of countif you can use Vlookup.

I am little bit confused about you intention but check if this help:

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Glenn RayExcel VBA DeveloperCommented:
Are you trying to determine the total number of unique values in column D?  OR, do you actually want a new listing of unique values?

If the latter, then the "Remove Duplicates" function will work for you:  just select that column as your defining criteria.

If the former, this formula will produce the total of unique values:

I confirmed this with a sample of 300000 records; you'll want to change the row range as needed.

Assuming your data is in a SQL table called IDNumbers with a column called IDNumber (this should work with most databases that support SQL - SQL Server, Access, mySQL, etc)

To generate a list of UNIQUE ID Numbers from your data and show the number of times each appears
SELECT IDNumber, COUNT(*) AS NumberOfOccurrences
FROM IDNumbers

Open in new window

If you only want those that appear multiple times - also showing how many times they appear
SELECT IDNumber, COUNT(*) AS NumberOfOccurrences
FROM IDNumbers

Open in new window

Just adding one more line to above comment
SELECT IDNumber, CASE WHEN COUNT(*) = 1 THEN 1 ELSE 0 END AS NumberOfOccurrences
FROM IDNumbers

Open in new window

Rory ArchibaldCommented:
If you can sort the data by column D first, you can use a much more efficient formula:


and copy down, then sum that column.

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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.