You made clever use of the SUMPRODUCT() function. Why didn't you continue this way?

The HOUR() function isn't needed.

`=SUMPRODUCT(WEEKDAY($A$1:$A$100;2)=$D4;MOD($A$1:$A$100;1)<0.5)`

would return the number of date-time-values belonging to the chosen weekday (D4) and being ante midiem.

`=SUMPRODUCT(WEEKDAY($A$1:$A$100;2)=$D4;MOD($A$1:$A$100;1)>=0.5)`

for post midiem.

Please also consider https://ask.libreoffice.org/en/questi.... We all should be interested in global cooperation.

You can also do it using COIUNTIFS(), of course. I personally don't like the functions using "Criteria" in that specific way too much because of the concept to convert to text for allowing different comparators (and probably converting back internally?). Using COUNTIF in an example specially designed in a ticklish way a few years ago, I got it to judge 143 out of thousand numbers to be greater than themselves.

**Clarifications and Corrections**

0. The mentioned example was from april 2014.

1. The wrongly counted numbers were 138 out of 1000 actually.

2. V 7.0 (and most likely earlier versions, too) do much better.

I tried the old example again and not a single one of the mentioned 138 errors was still showing after recalculation.

Your

`COUNTIFS`

formula doesn't work for several reasons.Function

`COUNTIFS`

expects ranges to apply a criterion to, but`WEEKDAY($A$1:$A$100,2)`

doesn't produce a range. it would produce a range (array), if something would force array evaluation just like`{=WEEKDAY($A$1:$A$100,2)}`

does. The common trick to force array evaluation is to use`SUMPRODUCT()`

Function

`COUNTIFS`

' criteria are wrong and must literally read`"=" & D4`

and`"<=12"`

/`">12"`

(including the double quotes: see LibreOffice Help - COUNTIFS function)Combining these:

`=SUMPRODUCT(COUNTIFS(WEEKDAY($A$1:$A$100;2);"=" & D4;HOUR($A$1:$A$100);"<=12"))`

would solve your issue. Please see my answer below.