# Count of Month

#### anneb_87

##### New Member
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

##### Well-known Member
Hi,

Not extremely clear ...

What is your expected result ?

#### anneb_87

##### New Member
this should be the result
 Normal 5 - received on May Early 2 - Received before May Late 6 - Received after May

#### sandy666

##### Well-known Member
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

Code:
``````// 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

##### Well-known Member
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

##### MrExcel MVP, Moderator
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
ABCDE
1File NameMayNormal
2A4/05/2019Early
3B4/05/2019Late
4C5/06/2019
5E5/06/2019
6F3/06/2019
7G2/05/2019
8H25/04/2019
9I25/04/2019
10J2/05/2019
11K2/05/2019
12L11/06/2019
13M11/06/2019
14N11/06/2019
15O11/06/2019
Count
Cell Formulas
Range(s)Formula
E1E1=SUMPRODUCT(--(MONTH(B2:B15)=MONTH("1"&B1)))
E2E2=SUMPRODUCT(--(MONTH(B2:B15)<MONTH("1"&B1)))
E3E3=SUMPRODUCT(--(MONTH(B2:B15)>MONTH("1"&B1)))

#### anneb_87

##### New Member
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

##### Well-known Member
update M-code for Power Query

Code:
``````// 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

##### New Member
Sorry I'm not familiar with Power Query

#### sandy666

##### Well-known Member
maybe time to learn
have a nice day