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

 

[James006]

Hi,

Not extremely clear ...

What is your expected result ?

 

[anneb_87]

this should be the result

Normal	5 - received on May
Early	2 - Received before May
Late	6 - Received after May

 

[sandy666]

with Power Query

File Name	May		Custom	Count
A	04/05/2019		Normal	5
B	04/05/2019		Late	7
C	05/06/2019		Early	2
E	05/06/2019
F	03/06/2019
G	02/05/2019
H	25/04/2019
I	25/04/2019
J	02/05/2019
K	02/05/2019
L	11/06/2019
M	11/06/2019
N	11/06/2019
O	11/06/2019

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"File Name", type text}, {"May", type date}}),
    Month = Table.AddColumn(Type, "Month", each Date.Month([May]), Int64.Type),
    Condition = Table.AddColumn(Month, "Custom", each if [Month] = 5 then "Normal" else if [Month] > 5 then "Late" else if [Month] < 5 then "Early" else null),
    Group = Table.Group(Condition, {"Custom"}, {{"Count", each Table.RowCount(_), type number}})
in
    Group

 

[James006]

Hi,

Thanks for clarification ...
Below are three array formulas which produce 5, 2 and 7 ...

In cell B17 =SUM(IF(MONTH($B$2:$B$15)=5,1,0))
In cell B18 =SUM(IF(MONTH($B$2:$B$15)<5,1,0))
In cell B19 =SUM(IF(MONTH($B$2:$B$15)>5,1,0))

Hope this will help

 

[Peter_SSs]

Another formula option that may be useful as it doesn't require modification if the B1 header is changed to a different month.
Assumption is that all dates are in the same year. If that is not always the case for you, some more clarification would help.

Book1
	A	B	C	D	E
1	File Name	May		Normal	5
2	A	4/05/2019		Early	2
3	B	4/05/2019		Late	7
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	2/05/2019
11	K	2/05/2019
12	L	11/06/2019
13	M	11/06/2019
14	N	11/06/2019
15	O	11/06/2019
Count

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(MONTH(B2:B15)=MONTH("1"&B1)))
E2	E2	=SUMPRODUCT(--(MONTH(B2:B15)<MONTH("1"&B1)))
E3	E3	=SUMPRODUCT(--(MONTH(B2:B15)>MONTH("1"&B1)))

 

[anneb_87]

Hi Experts, I forgot to mention there would be times that the data did not arrive or is not applicable for a month, how should I update the formula?

blanks are for data that was not received for that month
n/a are for data that is not applicable for that month

File Name	May
A	04-May-19
B	04-May-19
C	05-Jun-19
F	03-Jun-19
G	02-May-19
H	25-Apr-19
I	25-Apr-19
J
K	02-May-19
	n/a
M	n/a
N	11-Jun-19
O	11-Jun-1

Thanks Guys!

 

[sandy666]

update M-code for Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"File Name", type text}, {"May", type date}}),
    Month = Table.AddColumn(Type, "Month", each Date.Month([May]), Int64.Type),
    Condition = Table.AddColumn(Month, "Custom", each if [Month] = 5 then "Normal" else if [Month] > 5 then "Late" else if [Month] < 5 then "Early" else null),
    Filter = Table.SelectRows(Table.ReplaceErrorValues(Condition, {{"Custom", null}}), each ([Custom] <> null)),
    Group = Table.Group(Filter, {"Custom"}, {{"Count", each Table.RowCount(_), type number}})
in
    Group

 

[anneb_87]

Sorry I'm not familiar with Power Query

 

[sandy666]

maybe time to learn

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

have a nice day