Question

locodice

New Member
Joined
Jul 15, 2008
Messages
39
I have a column with various dates in e.g. 1/4/08 etc. Does anyone know a formula where I can count how many times each month of the year appears in the coloumn?

And, if there is column next to the dates one with cases in e.g. EF1, can I count how many times a case appears in each month of the year?

Any help will be great?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
=SUMPRODUCT(--(MONTH(A1:A100)=1))

Will count number of cells in A1:A100 are dates with the month of January.
 

locodice

New Member
Joined
Jul 15, 2008
Messages
39
Thanks. I take it if I put 2 instead of 1 it would count february and so on? I'm new to this.
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Yes, the formula will generate an array of months 1 through 12, the size of your range (100 values for A1:A100). It then compares each to the given number, which in my example is 1. Finally, it sums 1 for each comparison returning true, giving you the number of dates of that month.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,912
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top