How do I count totals of the number of occurrences of unique values in a column?

Question

How do I count totals of the number of occurrences of unique values in a column?

I have a spreadsheet with cases charged by prosecution, and I need to count how many arrest numbers there are - just a count of the total number, nothing else. Arrest numbers are listed in Column B. Each arrest number may occur more than once because there may be multiple charges for each arrest and each charge has its own row. So there may be one arrest, but the arrest number is listed 2-4 times because of multiple charges. Anyway, how do I just get a count of how many unique values occur in Column C, thereby indicating how many unique arrest numbers there are (excluding the duplicate occurrences)?

I have read many other related posts but have not found this.

Explanation / Answer

======= Hi,

T&ry this. If 1000 rows isn't enough then increase the rows in 3 places, they must all be the same size. The formula ignores blanks so you can make the range large enough for additions.

=SUMPRODUCT((B1:B1000<>"")/COUNTIF(B1:B1000,B1:B1000&""))

Or5:"

For unique values in column B -

=SUMPRODUCT((B2:B10000<>"")/COUNTIF(B2:B10000,B2:B10000&""))

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.