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,770
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,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,231
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
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,770
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,081,765
Messages
5,361,156
Members
400,615
Latest member
inzimam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top