Ask Your Question

COUNTIFS to get weekdays split in am/pm count

asked 2020-08-13 16:10:57 +0200

BloodAngelScriptor gravatar image


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?

edit retag flag offensive close merge delete


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.

Opaque gravatar imageOpaque ( 2020-08-13 17:30:51 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-08-13 17:27:46 +0200

Lupp gravatar image

updated 2020-08-14 10:58:53 +0200

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

edit flag offensive delete link more


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

BloodAngelScriptor gravatar imageBloodAngelScriptor ( 2020-08-14 08:03:25 +0200 )edit

answered 2020-08-13 17:03:48 +0200

Opaque gravatar image

updated 2020-08-13 17:06:12 +0200



=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:; 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

edit flag offensive delete link more


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.

BloodAngelScriptor gravatar imageBloodAngelScriptor ( 2020-08-14 08:02:22 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2020-08-14 12:11:19 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-13 16:10:57 +0200

Seen: 71 times

Last updated: Aug 14 '20