CODEWITHSUNDEEP
excelexcel-formula
Sam Brightman
Sam Brightman

Reputation: 2950

Count distinct, exact string values in Excel

I've seen many examples of questions on Stack Exchange and elsewhere around the web of people wanting to count distinct values in an Excel range using a formula. Newer versions of Excel have the DISTINCT function but I'm not yet on Excel 2013.

This answer shows an elegant formula:

SUM(1/COUNTIF(range, range))

As an array formula, this counts the distinct values but will use Excel's default case-insensitive comparison. All the other examples that I've found (mostly variants on SUMPRODUCT and COUNTIF expressions) are also case-insensitive. Is there a simple way to use EXACT or similar functionality within the COUNTIF and force a case-sensitive unique count?

Upvotes: 1

Views: 1663

Answers (1)

Gary's Student
Gary's Student

Reputation: 96763

To count distincts in A1 thru A100 that is case sensitive use the array formula:

=SUM(IFERROR(1/IF($A$1:$A$100<>"", FREQUENCY(IF(EXACT($A$1:$A$100, TRANSPOSE($A$1:$A$100)), MATCH(ROW($A$1:$A$100), ROW($A$1:$A$100)), ""), MATCH(ROW($A$1:$A$100), ROW($A$1:$A$100))), 0), 0))

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Upvotes: 2

Related Questions