Note that ignores any and all errors, not just a #DIV/0! because of an empty cell.
This does not answer the question “How does it work?”, which already was answered by others. This explains the reasons for getting wrong (and fractional) resulst under certain conditions. After all, the errors were the reason to ask the question. I will edit the question slightly to make this clear.
The fractional parts are due to unsymmetric cases where a COUNTIF(Cell;Cell) with twice the same cell would return 0, or where an empty string is accepted as a match for blank but not reversely. (Similar with 0-values in some cases).
The first variant won’t occur with exact matching for strimgs, but if the Option
>Tools>Options>LibreOffice Calc>Calculation>>Formulas Wildcards>>>"Enable regular expressions in formulas"
is enabled strings containing special characters of RegEX can as well spoil the recognition of the string itself as a match, as also count a not exact match as if it was exact.
To a reduced extent such an effect can also occur with Wildcards (MS style) enabled.
A different setting may also cause related issues: Search criteria....whole cells not checked.
In the given case the “unescaped parentheses” were “the culprit”.
Conclusion: Always consider thoroughly the options you enable/disable.
Bad: RegEx (or Wildcad) can only be enabled/disabled per documnent. Actually needed is a way to decide it per formula where functions are capable of RegEx (Wildcard) sensitivity.
Also: Try to work without tricks. Counting should be done by counting (1 by 1), not by adding fractions.
[Edit 2021-06-09 about 11:15 GMT: The announced user function]
Option VBAsupport 1
Function countDistinct1D(pRange)
countDistinct = -1
rg = pRange.CellRange
da = rg.getDataArray()
uR = Ubound(da)
Dim args(0) As New com.sun.star.beans.PropertyValue
args(0).Name = "Hidden"
args(0).Value = True
hDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, args)
hSheet = hDoc.Sheets(0)
hRg = hSheet.getCellRangeByPosition(0, 0, 0, uR)
hRg.setDataArray(da)
fd = hRg.CreateFilterDescriptor(True)
Dim ffs(0) As New com.sun.star.sheet.TableFilterField
ffs(0).IsNumeric = False
With fd
.SkipDuplicates = True
.CopyOutputData = True
tRa = .OutputPosition
tRa.Sheet = 0
tRa.Column = 1
tRa.Row = 0
.OutputPosition = tRa
.FilterFields = ffs
.FilterFields(0).IsNumeric = False
End With
hRg.Filter(fd)
hRg.clearContents(7)
cc = hSheet.createCursor()
cc.gotoStartOfUsedArea(False)
cc.gotoEndOfUsedArea(True)
countDistinct1D = cc.RangeAddress.EndRow + 1
hDoc.Close(True)
fail:
End Function
(It’s actually tested!)
[/Edit]
I just checked, I actually had “enable wildcards in formulas” checked, which seems to be the default, because I surely never changed these settings, not understanding their consequences
“Also: Try to work without tricks. Counting should be done by counting (1 by 1), not by adding fractions.”
How can I do this?
I gave all the options above.
Including speed. Thus, I answered a question that was not asked, but “was in the air”
Actually, the Frequency/Match approach is pretty good as it doesn’t come up with decimals but integers. It came up with a different result (1 item less) on a list of 66,000 entries. I use this now. In terms of speed, I notice no difference though, I can go and make coffee in both cases before the process is finished. 
Be cautious:
The working of MATCH() also basically depends on the setting concerning “Wildcards” or “Regular expressions” or “Neither nor” (radio biuttons).
All the solutions suggested by @eeigor use a function of that famiily.
If somebody worries about the question if a result of any “countdistinct” formula is correct, it’s necesary to use the standard filter without any criterion, but wit No duplicates enabled under the options. There also is an option to output the filtered result elsewhere (given by the top-left cell of a range assured to be empty in advance - otherwise overwriting will occur). The results there are all unique now, and can simply be counted using COUNTA().
For specifically interesd users I will append to my answer a user Function based on ideas probably looking strange, but providing te fastest solution I can offer.
Indeed, with regular expressions switched on and items containing operators or metacharacters items can be counted multiple times, resulting in unexpected fractional values, or not be counted at all. For this the search criteria can be forced to a quoted literal string by prepending \Q and appending \E metacharacters, so that would be
=SUM(1/COUNTIF(B2:B400,"\Q"&B2:B400&"\E"))
(not tested with your document but in general works).
Note that if Excel compatible Wildcards are enabled instead of regular expressions, there is no such simple remedy. And the formula does also not work with no wildcards or regular expressions enabled.
“use the standard filter without any criterion, but wit No duplicates enabled under the options. There also is an option to output the filtered result elsewhere (given by the top-left cell of a range assured to be empty in advance - otherwise overwriting will occur). The results there are all unique now, and can simply be counted using COUNTA()”
I have tried this before and now again, but that doesn’t work for me. I get only the first row copied to a new sheet, nothing else. With advanced filter, I get an error message. 
“For this the search criteria can be forced to a quoted literal string by prepending \Q and appending \E metacharacter”
Gosh, there is so much to know for such a simple operation. How can a standard user who does something more than just basic operations only once a year or so, ever be proficient in that?! I mean without you guys I would have had no way to know or ever find out. That’s kind of disturbing…
@Tass wrote: “I get only the first row copied to a new sheet, nothing else”.
Filters are fine. The range must be contiguous, that is, no blank lines.
I have tried this {stanard filter with
No duplicates} before and now again, but that doesn’t work for me.
It works for me, and it works for the user function I posted. We are using the same software. You must have missed the “No criterion at all” or you misinterpreted it. Select “-none-” also for the very first field.
As already posted in different places: The choice concerning wildcards and regular expressions must be offered per function call. A trailing optional parameter would do. As long as Excel doesn’t start to misuse that position, it would even not reduce compatility beyond what’s unavoidable anyway due to the shortcomings of Excel.
Powerful means require some studies concerning their handling.
If you definitely want to omit regular expressions and wildcards, you can make the respective setting for your local LibO (in the place I already described).
However, there is a bug. Clear the field name in the first condition of the standard filter. I also have only the title row left. Just select “-none-“
The choice concerning wildcards and regular expressions must be offered per function call. A trailing optional parameter would do. As long as Excel doesn’t start to misuse that position, it would even not reduce compatility beyond what’s unavoidable anyway due to the shortcomings of Excel.
Not true. As soon as the optional parameter is used you can’t export the function to Excel (or any other spreadsheet implementation) anymore. (well, you could, but it would fail there).
there is so much to know for such a simple operation. How can a standard user who does something more than just basic operations only once a year or so, ever be proficient in that
The standard user does not switch away from the default Wildcards setting and thus “only” has the same standard problems a standard Excel user has.
Not true. As soon as the optional parameter is used you can’t export the function to Excel (or any other spreadsheet implementation) anymore. (well, you could, but it would fail there).
You can export without the optional parameter. And any formula actually depending on RegEx won’t work as expected in Excel anyway. To leave it to Excel’s defaults shouldn’t mess up things worse than they are messed up by any export to hostile formats an by passing clever solutions to improper software.
Compatibility isn’t a solution. It’s the problem if those you try to be compatible with know the tricks to make you fail. They aren’t even expected to be compatible with themselves by the public.You can’t solve a problem of social and/or political disorder by technical means.
The only way for free software is to be unmistakably better - not only concerning the features, but mainly the relevant aspects … You know. Deciders don’t yet. That’s the problem.
Ok Lupp and eeigor, the first field name was automatically filled in. After setting that to “none” it works like a charm. Thanks.
Btw, is there a way to get the items listed that any of the formulas eeigor posted counted? Just so that I could compare what the formula counts to what the filter comes up with? (Meaning to see which one was left out or counted more than once).
You can export without the optional parameter.
If exported without then you also can’t read it back in and you’d have to switch on the global option anyway to make the document work again.
Adding implementation dependent optional parameters to standardized functions is a mess and I strongly advise against.