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