Count Number of times a month appears in a range of dates

ryan.katy

New Member
Joined
Jul 23, 2008
Messages
6
Hi All,

I have a very large spreadsheet consisting of cycle times and dates. I need to count how many time times a specific cycle was performed within certain months.

I have tried everything I can think of but nothing seems to be working.

I've tried using COUNTIF etc but nothing!!!

Can any of you please help!???!!!:confused:

Regards,
K.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi, Sorry about that.

Say for example I need to count how many times BD takes place in the month of April? Multilple cycles take place on the same day.

Column A Column B
28-Apr-08 BD
29-Apr-08 BD
29-Apr-08 FS
29-Apr-08 WU
30-Apr-08 LT
30-Apr-08 BD
1-May-08 WU
1-May-08 WU
2-May-08 BD

Thanks a million,
Katie
 
Upvote 0
Enter the month (in numbers 4 for April) in Cell C1 and enter the cycle in Cell D1. Use the below formula
=SUMPRODUCT(--(MONTH(A1:A9)=C1)*--(B1:B9=D1))

Hope this is as per your expectation.
 
Upvote 0
Hey,

Yeah I need to count it twice.

At the moment I have a countif defining the range of dates and searching for BD say within that range. But cycle programs are ever changing!!!
 
Upvote 0
If you have fixed month and cycle times then you can use the formula mentioned already.
But if it is dynamic, best way of handle this is to use Pivot table. Once you have created a pivot table (except the range changes) you can just refresh to get the details required.

Hope this is useful for you.
 
Upvote 0
Hey,

Yeah I need to count it twice.

At the moment I have a countif defining the range of dates and searching for BD say within that range. But cycle programs are ever changing!!!
Code:
=SUMPRODUCT(
  --($A$2:$A$20-DAY($A$2:$A$20)+1=E2),
  --($B$2:$B$20=F2))

where E2 houses a month/year of interest in the form of:

1-Apr-08

meaning Apr/2008

and F2 a value like BD.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top