Count of Month

anneb_87

New Member
Joined
Jun 13, 2018
Messages
43
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 NameMay
A04-May-19
B04-May-19
C05-Jun-19
E05-Jun-19
F03-Jun-19
G02-May-19
H25-Apr-19
I25-Apr-19
J02-May-19
K02-May-19
L11-Jun-19
M11-Jun-19
N11-Jun-19
O11-Jun-19
Normal???
Early???
Late???
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,666
Hi,

Not extremely clear ... ;)

What is your expected result ?
 

anneb_87

New Member
Joined
Jun 13, 2018
Messages
43
this should be the result
Normal5 - received on May
Early2 - Received before May
Late6 - Received after May
:)
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,292
with Power Query

2File NameMayCustomCount
3A04/05/2019Normal5
4B04/05/2019Late7
5C05/06/2019Early2
6E05/06/2019
7F03/06/2019
8G02/05/2019
9H25/04/2019
10I25/04/2019
11J02/05/2019
12K02/05/2019
13L11/06/2019
14M11/06/2019
15N11/06/2019
16O11/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
Joined
Apr 4, 2009
Messages
3,666
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
Joined
May 28, 2005
Messages
41,790
Office Version
365
Platform
Windows
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.

xl2bb.xlam
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
Joined
Jun 13, 2018
Messages
43
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 NameMay
A04-May-19
B04-May-19
C05-Jun-19
F03-Jun-19
G02-May-19
H25-Apr-19
I25-Apr-19
J
K02-May-19
n/a
Mn/a
N11-Jun-19
O11-Jun-1


Thanks Guys!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,292
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
 

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top