Counting days

[Jayfaku]

In column B2:B157 I have dates
In column C2:C157 I have =CHOOSE(WEEKDAY(B3),"Sun","Mon","Tue","Wed","Thu","Fri","Sat","Sun"), to give me the day, B3 is a specific date

What I want to do in another worksheet is count number of mon, tues, wed,... in a given month.

So the result should tell me 5 reports made on Mon in June, etc.....

Can you please help?

 

[Logit]

.
Paste in F2 and copy down :

=COUNTIF(C2:C157,E2)

	A​	B​	C​	D​	E​	F​
1​		​	​
2​		1/1/2018​	Tue​		Mon	22​
3​		1/2/2018​	Wed​		Tue	22​
4​		1/3/2018​	Thu​		Wed	21​
5​		1/4/2018​	Fri​		Thu	21​
6​		1/5/2018​	Sat​		Fri	21​
7​		1/6/2018​	Sun​		Sat	22​
8​		1/7/2018​	Mon​		Sun	21​
9​		1/8/2018​	Tue​
10​		1/9/2018​	Wed​

 

[Peter_SSs]

In column B2:B157 I have dates
In column C2:C157 I have =CHOOSE(WEEKDAY(B3),"Sun","Mon","Tue","Wed","Thu","Fri","Sat","Sun"), to give me the day, B3 is a specific date

If you really need this (text) column, a much simpler formula is

=TEXT(B3,"ddd")

.. count number of mon, tues, wed,... in a given month.

Can you confirm whether you want ..
a) The actual number of Mondays in a calendar of a particular month, or
b) The number of dates in your list B2:B157 that are actually, say, a Monday in June? If this is it, does your list of dates cover dates in more than one year so that you might have 4 June 2018 and 26 June 2016 and if so, might we count both of thos if looking at June or would we be just looking at, say, June 2018?

 

[Jayfaku]

The year runs from July 2017 to June 2018, so month is not repeated.
The result should give me NUMBER of reports in June that was on Mon, Tues...

Just to help me understand which day it is being reported I have entered the day formula in column C

Thanks

 

[Peter_SSs]

Just to help me understand which day it is being reported I have entered the day formula in column C

Fair enough, though if you want that, the formula I gave is a very simple one to do that.

For your counting question I have used those column C values in my column F formula below, but note the alternative (shorter) formula in column G that doesn't require column C to get the same result.

Excel Workbook
	B	C	D	E	F	G
1					June
2	1/05/2018	Tue		Mon	2	2
3	3/05/2018	Thu		Tue	1	1
4	5/05/2018	Sat		Wed	2	2
5	7/05/2018	Mon		Thu	1	1
6	9/05/2018	Wed		Fri	1	1
7	11/05/2018	Fri		Sat	2	2
8	13/05/2018	Sun		Sun	1	1
9	15/05/2018	Tue
10	17/05/2018	Thu
11	19/05/2018	Sat
12	21/05/2018	Mon
13	23/05/2018	Wed
14	25/05/2018	Fri
15	27/05/2018	Sun
16	29/05/2018	Tue
17	31/05/2018	Thu
18	2/06/2018	Sat
19	4/06/2018	Mon
20	6/06/2018	Wed
21	8/06/2018	Fri
22	10/06/2018	Sun
23	12/06/2018	Tue
24	14/06/2018	Thu
25	16/06/2018	Sat
26	18/06/2018	Mon
27	20/06/2018	Wed
Dates