Hi Hector,

I would gladly follow your advice if I only knew how to insert your macro? in
Worksheet_Change event.
And if I do where would the value show?

Thanks


"Hector" wrote:

Hi, try this. Pop it in the Worksheet_Change event.

Dim rgN As Range

Application.EnableEvents = False

If Target.Column = 1 Then
Set rgN = Range("A1", Range("a65536").End(xlUp).Address)
Cells(1, 2).Value = _
"=SUMPRODUCT((" & rgN.Address & "<" & Chr$(34) & Chr$(34) & _
")/COUNTIF(" & rgN.Address & "," & rgN.Address & _
"&" & Chr$(34) & Chr$(34) & "))"
Set rgN = Nothing
End If

Application.EnableEvents = True


"Leon" wrote:

I have the following formula:
=SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&""))

This formula counts the number of unique records in a column (A)

The column (A) grows every day to a variable amount I need to replace the
(100) by the value of a cell that counts the row in column (A)

I tried all the variations of "INDIRECT) but it doesn't seem to work.
Obviously my syntax is not right.

Any pointer will be gratefully appreciated.

Thanks in advance