Count distinct values in a filtered list (Libre Office Calc)

Question

I want to count distinct values (dates) in the range B23:B2000 in Libre Office Calc.

Additionally the result should change if a filter is applied.

The range B23:B2000 consists of real dates (e.g. in B23:B34) and of "" for the rest (e.g. B35:B2000), which is created through the formula

=IF($Temp.V35<>"",$Temp.V35,"")

to get empty cells if there is no raw data in the source sheet "Temp".

The value "" should not be counted as distinct value in the end result. I assume one could just subtract 1 from the result to get the correct one?

I tried different array formulas but I cannot get the formula to work for a filtered list.

=SUM(IF(ISBLANK(B23:B2000),"",1/COUNTIF(B$23:B$2000,B23:B2000)))

or

=SUMPRODUCT((B23:B2000<>"") / COUNTIF(B23:B2000,B23:B2000 & ""))

Thank you very much in advance for your help!

Answer 1

I see these ways for you:

(A) Define "unique" in the 'Standard Filter' itself and count the values by function SUBTOTAL, which reacts on filtering.

(B) Define "unique" in the 'Standard Filter' itself. Let the filter result be written on a separate sheet (which you may hide) and count the values there. You need to refresh the source data range after changes on filter or source values.

(C) Do filtering directly with the FILTER function, and then count with COUNT(UNIQUE(...)). That would be something like =COUNT(UNIQUE(FILTER(B23:B2000; B23:B2000>=DATE(2024;2;18)))) The functions FILTER, UNIQUE (and others) are available in LibreOffice 24.8. That is currently in prerelease and will come soon.

UNIQUE will not help you with a range filtered by 'Standard Filter', because it returns the values too that are filtered out.

Answer 2

I found a solution with multiple helper columns from here:

https://contexturesblog.com/archives/2010/10/04/count-unique-items-in-excel-filtered-list/

There are multiple other solutions without helper columns but only with functions that work only for Excel and not for Libre Office Calc (e.g. UNIQUE).