Count of Month

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
how should I update the formula?
These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

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
10J
11K2/05/2019
12L
13M
14N11/06/2019
15O11/06/2019
Count (2)
Cell Formulas
Range(s)Formula
E1E1{=COUNT(1/((MONTH(B2:B15)=MONTH(1&B1))*(B2:B15<>"")))}
E2E2{=COUNT(1/((MONTH(B2:B15)<MONTH(1&B1))*(B2:B15<>"")))}
E3E3{=COUNT(1/((MONTH(B2:B15)>MONTH(1&B1))*(B2:B15<>"")))}
B12:B13B12=NA()
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

anneb_87

New Member
Joined
Jun 13, 2018
Messages
49
These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

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
10J
11K2/05/2019
12L
13M
14N11/06/2019
15O11/06/2019
Count (2)
Cell Formulas
Range(s)Formula
E1E1{=COUNT(1/((MONTH(B2:B15)=MONTH(1&B1))*(B2:B15<>"")))}
E2E2{=COUNT(1/((MONTH(B2:B15)<MONTH(1&B1))*(B2:B15<>"")))}
E3E3{=COUNT(1/((MONTH(B2:B15)>MONTH(1&B1))*(B2:B15<>"")))}
B12:B13B12=NA()
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
It works. Thanks!!
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

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