Count of Month

[anneb_87]

Hi Experts!

May I know what formula in excel if I need to get the count of the data that comes in early and late for a month

File Name	May
A	04-May-19
B	04-May-19
C	05-Jun-19
E	05-Jun-19
F	03-Jun-19
G	02-May-19
H	25-Apr-19
I	25-Apr-19
J	02-May-19
K	02-May-19
L	11-Jun-19
M	11-Jun-19
N	11-Jun-19
O	11-Jun-19
Normal	???
Early	???
Late	???

 

[Peter_SSs]

how should I update the formula?

These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

Book1
	A	B	C	D	E
1	File Name	May		Normal	4
2	A	4/05/2019		Early	2
3	B	4/05/2019		Late	5
4	C	5/06/2019
5	E	5/06/2019
6	F	3/06/2019
7	G	2/05/2019
8	H	25/04/2019
9	I	25/04/2019
10	J
11	K	2/05/2019
12	L	#N/A
13	M	#N/A
14	N	11/06/2019
15	O	11/06/2019
Count (2)

Cell Formulas
Range	Formula
E1	E1	{=COUNT(1/((MONTH(B2:B15)=MONTH(1&B1))*(B2:B15<>"")))}
E2	E2	{=COUNT(1/((MONTH(B2:B15)<MONTH(1&B1))*(B2:B15<>"")))}
E3	E3	{=COUNT(1/((MONTH(B2:B15)>MONTH(1&B1))*(B2:B15<>"")))}
B12:B13	B12	=NA()
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[anneb_87]

maybe time to learn

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

have a nice day

That would be great, I would love that but our company does not have it and I think they would not purchase it. Anyways, Thanks Sandy!!

 

[anneb_87]

These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

Book1
	A	B	C	D	E
1	File Name	May		Normal	4
2	A	4/05/2019		Early	2
3	B	4/05/2019		Late	5
4	C	5/06/2019
5	E	5/06/2019
6	F	3/06/2019
7	G	2/05/2019
8	H	25/04/2019
9	I	25/04/2019
10	J
11	K	2/05/2019
12	L	#N/A
13	M	#N/A
14	N	11/06/2019
15	O	11/06/2019
Count (2)

Cell Formulas
Range	Formula
E1	E1	{=COUNT(1/((MONTH(B2:B15)=MONTH(1&B1))*(B2:B15<>"")))}
E2	E2	{=COUNT(1/((MONTH(B2:B15)<MONTH(1&B1))*(B2:B15<>"")))}
E3	E3	{=COUNT(1/((MONTH(B2:B15)>MONTH(1&B1))*(B2:B15<>"")))}
B12:B13	B12	=NA()
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

It works. Thanks!!

 

[Peter_SSs]

It works. Thanks!!

You're welcome. Thanks for the confirmation.