A formula to sum unique values within a dynamic range

From what I can see, this is summing unique numbers in column A, which can contain either text or numbers. It's probably VERY slow as it's calculating down more than 1,000,000 rows. Do you really need that number of rows? So, what do you mean by an "expanding dataset"? If you mean that today there are 20 rows of data and tomorrow, 30 and the nxt day 40, then create a named range.

here I created a Named range (called range) using the formiula:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$1000,COUNTA(Sheet1!$A$2:A$1000))

Your formula then becomes:

=SUMPRODUCT(1/COUNTIF(Range,Range&""),Range)

and only looks down column A as far as the last non-blank cell.

Here's a more efficient method.

Create the dynamic range...

Name: Range (or whatever you want to call it)
Refers to: =$A$1:INDEX($A:$A,MATCH(1E100,$A:$A))

Then, the sum formula would be:

=SUM(IF(FREQUENCY(Range,Range),Range))

You're welcome. Thanks for the feedback!