You made clever use of the SUMPRODUCT() function. Why didn’t you continue this way?
The HOUR() function isn’t needed.
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 Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?. 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.
- The wrongly counted numbers were 138 out of 1000 actually.
- 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.