On 2002-03-22 03:16, Ian Mac wrote:

I have the following formula is return the number of unique entries in a list:

=IF(LEN(Listing!I2:I845),SUMPRODUCT(1/COUNTIF(Listing!I2:I845,

)))

Which works fine:

the problem being the list varies in length, so I've added dynamic ranges to the formula:

=IF(LEN(Address),SUMPRODUCT(1/COUNTIF(Address,Address)))

Where 'Address' is =OFFSET(Listing!$I$2,0,0,COUNTA(Listing!$I:$I),1).

but now the formula doesn't work, BUT if I change the formula to:

=IF(LEN(Address),SUMPRODUCT(1/COUNTIF(Address,Listing!I2:I845)))

where the last arg in the COUNTA isn't the named range it works.

Any idea??

## Like this thread? Share it with others