hi,

=COUNTIF(A1:A65535,A1:A65535)-1

isabelle

Le 2016-08-04 Ã* 09:24, pete a écrit :
I need a formula, not VB code, to count the number of unique values in a
column of up to 10,000 rows, some of which will be blank. The values may be
numeric, alphanumberic, or text.

The twist is that the formula can't use either the SUMPRODUCT or the
FREQUENCY functions. The excel handler which processes the file for a
database can't handle SUMPRODUCT or FREQUENCY.

I got very close with this array formula: =SUM(1/COUNTIF(A1:A36,A22:A36))
But as you can see it has to be limited to the range of nonblank cells, since
it throws a #DIV/0 error if the range includes blank cells.

I've made some unsuccessful attempts at using COUNTIFS with criteria for only
nonblank cells.

Any ideas?

Thanks in advance!

pete