SUMPRODUCT command is not working on a large list
Hello guys,
I was trying to count the number of unique values in a range, with using the following formula:
however, it seems this formula is not working on a large list since I got 447,148 values in my list,
=SUMPRODUCT(1/COUNTIF(A1:A447148,A1:A447148))
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.
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
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I produced a list of 50000 and it took 5 minutes to calculate....nearly 500000 would take a LOT longer!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks