|
|
Text and/or Numerical Values -
One Dimensional Range
The
following formula counts the number of unique values in
B2:B10...
=SUM(IF(FREQUENCY(IF(B2:B10<>"",MATCH("~"&B2:B10,B2:B10&"",0)),ROW(B2:B10)-ROW(B2)+1),1))
| Note
that this is an array formula, which needs
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
Based on the sample data, the formula returns 4. Sample
Workbook
|
Text and/or Numerical Values -
Two Dimensional Range
The
following formula counts the number of unique values in
B2:C10...
=SUM(IF(B2:C10<>"",1/COUNTIF(B2:C10,B2:C10)))
| Note
that this is an array formula, which needs
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
Based on the sample data, the formula returns 4. Sample
Workbook
|
Numerical Values
The
following formulas count the number of unique numerical
values in B2:B10...
=SUMPRODUCT(--(FREQUENCY(B2:B10,B2:B10)>0))
or
=SUM(IF(FREQUENCY(B2:B10,B2:B10),1))
| Note
that this is an array formula, which needs
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
Based on the sample data, the formulas return 3. Sample
Workbook
|
|
|
