COUNTIFS to get weekdays split in am/pm count

[BloodAngelScriptor]

Hi,

I’m trying to count the occurrence of every weekday split into am and pm.

Just summing the weekdays works fine with =SUMPRODUCT(–(WEEKDAY(A1:A100,2)=D4)) and the help column.

But I’m not able to figure out why F4 doesn’t work to get the Monday AM count.
=COUNTIFS(–(WEEKDAY($A$1:$A$100,2)),=D4,(HOUR($A$1:$A$100)<12))

Can somebody help me please?

[anon73440385]

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.

[Lupp]

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 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.

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.

[BloodAngelScriptor]

Thank you very much for the additional explanation. Problem solved!

[anon73440385]

Hello,

try:

`=SUMPRODUCT(WEEKDAY($A$1:$A$100;2)=D4;HOUR($A$1:$A$100)<=12)` (am) `=SUMPRODUCT(WEEKDAY($A$1:$A$100;2)=D4;HOUR($A$1:$A$100)>12)` (pm)

PS: I don’t understand your usage of -- and the additional parenthesis ( and )

Tested using LibreOffice:

Version: 6.4.6.2; Build ID: 0ce51a4fd21bff07a5c061082cc82c5ed232f115
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US; Calc: threaded

Hope that helps

[BloodAngelScriptor]

Thank you. I’m kinda new to calc/spreadsheets and still learning. I just adapted the “–” from the “=SUMPRODUCT(–(WEEKDAY(A1:A100,2)=D4))” formula.

[mikekaganski]

I just adapted the “–” from the “=SUMPRODUCT(–(WEEKDAY(A1:A100,2)=D4))” formula.

… which is itself unclear - why the --(...) was needed there.

Seems that is an artifact from Excel, where summing booleans might not result in sensible things…