0
For example I have a list of dates in A2 through A31 (1st April to 30th April) and want to count the number of weekdays.
In B2 to B31 I could use =WEEKDAY(A2,2)<6 to give true or false. Then separately count the trues.
Is there anyway to do it all in 1 cell with something similar to =COUNTIF(A2:A31,WEEKDAY(A2,2)<6)?
Sam Dean
Posted 2018-04-10T15:27:39.350
Reputation: 139
1
Sorry if this in inappropriate but in the answers of a "similar question" I found this unaccepted answer that is solving a different problem but also solved mine.
I don't think it's an obvious link between this question and what he was answering so I posted the question anyway and here is the answer that solved it for me.
You can give WEEKDAY() an array which isn't documented on Microsoft's website.
So the following worked for me. =SUMPRODUCT(1*(WEEKDAY(A2:A31,2)<6))
Changed -- to 1* thanks to Bandersnatch
Sam Dean
Posted 2018-04-10T15:27:39.350
Reputation: 139
1Beat me by 14 seconds. :-) I like to use 1* instead of the double negation: =SUMPRODUCT(1*(WEEKDAY(A1:A30,2)<6)). I just think it looks cleaner. – Bandersnatch – 2018-04-10T15:33:31.910
and another: https://superuser.com/questions/866590/excel-how-to-count-cells-whose-date-values-represent-a-day-between-monday-and
– Scott Craner – 2018-04-10T15:37:00.697