What formula in LibreOffice Calc will count the number of distinct Week_Number occurrences? The answer should be 2.
Add a comment
|
3 Answers
This question has been asked many times before. One way is to enter the following formula in B6 and then press Ctrl+Shift+Enter:
=SUM(1/COUNTIF(B2:B5,B2:B5))
Other solutions are described at:
- https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=47223
- https://superuser.com/questions/238656/openoffice-get-distinct-values-from-column
- https://askubuntu.com/questions/114732/different-values-in-one-column
I prefer using a database such as LibreOffice Base instead, with a simple query:
SELECT COUNT(DISTINCT Week_Number) FROM Weeks_Table;
-
4Thanks! I would like to emphasize how important seems to be to enter the formula and then press the command
Ctrl+Shift+Enter. It took me several attempts because I tried copying and pasting and other kinds of shortcuts to no avail. Commented Jun 30, 2020 at 10:39 -
I was getting confused about the key combination too; apparently the combo creates an array function - reference help.libreoffice.org/7.1/en-US/text/scalc/01/…. Also, the key combination on Mac is
Cmd+Shift+Enter.– VicCommented Apr 12, 2021 at 18:07
I tried using =SUM(1/COUNTIF(A:A,A:A)) on a column with 2500 rows, and got a divide by zero error for my trouble, (After hours of computation time.). Then I thought of:
=SUMPRODUCT( A$3:A1000000<>OFFSET(A$3:A1000000,-1,0)) which works to find the number of distinct values in SORTED column A2... by finding when the value in cell Ax is different than the value in the cell directly above it (OFFSET(...,-1,0)).
Now if we only had a SORT array function this problem would be solved.
Take this gift =AGGREGATE(9,2,(1/COUNTIF(E4:E803,E4:E803)))
-
Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? Commented Oct 20, 2023 at 0:43