Count of Month

anneb_87

New Member
Joined
Jun 13, 2018
Messages
49
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,680
Hi,

Not extremely clear ... ;)

What is your expected result ?
 

anneb_87

New Member
Joined
Jun 13, 2018
Messages
49
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,851
with Power Query

File NameMayCustomCount
A04/05/2019Normal5
B04/05/2019Late7
C05/06/2019Early2
E05/06/2019
F03/06/2019
G02/05/2019
H25/04/2019
I25/04/2019
J02/05/2019
K02/05/2019
L11/06/2019
M11/06/2019
N11/06/2019
O11/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,680
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
42,315
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.

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
Joined
Jun 13, 2018
Messages
49
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,851
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,082,573
Messages
5,366,372
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top