1

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?

Community
  • 1
  • 1
Sam Brightman
  • 2,831
  • 4
  • 36
  • 38

1 Answers1

2

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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99