Hello there.
I have a formula that counts the unique values from a column:
=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))-1
The file is small : contains ~150 records , but when i open it in libre calc it takes a very long time (10 minutes - with libre in “Not responding” state)
OS:windows 8
I have uploaded the file here : http://www.metrici.ro/cars_statistics_2014-02-14.xls
I downloaded the file on my desktop and started by double clicking on the file. Even large xls-files open immediately. Therefore I am afraid that there is a problem within the file and not in LibO.
After saving the file in ods-format it took a very long time to open the file again.
I disabled the formulas you added into F2 and F3 by adding a character in front of “=”, saved in ods and it opend immediatley. Next I saved in xls and it opened immediately.
Thus, I conclude it is your formula. However I don’t really understand what you want to achieve with these formulas, thus cannot comment on them.
I think the issue is in relation with the incompatibility of the excel column range A:A, transformed opening in LibreOffice in A1:A1048576 what carry on a very long calculation for the formula like your, if I am right calculating for every row in the first array 1048576, 1048576x1048576 countif. (1048576^3)
Try setting up a range large enough for your data, and I hope the issue will be solved, working on both programs.
If I’m not wrong the formula is for calculate how many different values in the range.
indeed after some patience(20 minutes) the formula was transformed into A1:A1048576
Any alternative method to get all the unique counts of a value from a column without knowing how many rows will be in the column from the beginning ?