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