SUMPRODUCT command is not working on a large list

[phantomcy]

Hello guys,

I was trying to count the number of unique values in a range, with using the following formula:

Please Login or Register  to view this content.

however, it seems this formula is not working on a large list since I got 447,148 values in my list,
the formula just return to 0 after it takes a minute to calculate the value.
So is there are anyway to count the number of distinct value in a list ?

Thank you.

[AlKey]

Try this array formula

=SUM(IF(FREQUENCY(IF($A1:$A447148<>"",MATCH($A1:$A447148,$A1:$A447148,0)),ROW($A1:$A447148)-MIN(ROW($A1:$A447148))+1),1))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

[newdoverman]

That is an awful lot of calculation to do. It will likely take far longer than 1 minute. I tried it on my laptop and had filled 447,148 values in column A and after 5 minutes, the computer was still running like mad without results. I eventually just killed the process.

[newdoverman]

I produced a list of 50000 and it took 5 minutes to calculate....nearly 500000 would take a LOT longer!