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

This is a discussion on Count Number of times a month appears in a range of dates within the Excel Questions forums, part of the Question Forums category; Hi All, I have a very large spreadsheet consisting of cycle times and dates. I need to count how many ...

1. ## Count Number of times a month appears in a range of dates

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!!!

Regards,
K.

2. ## Re: Count Number of times a month appears in a range of dates

Please give a set of example for better understanding.

3. ## Re: Count Number of times a month appears in a range of dates

Try this,

=SUMPRODUCT((RANGE_of_DATES>=BDate)*(RANGE_of_DATES<=EDate))

Regards

4. ## Re: Count Number of times a month appears in a range of dates

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

5. ## Re: Count Number of times a month appears in a range of dates

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.

6. ## Re: Count Number of times a month appears in a range of dates

Originally Posted by ryan.katy
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.
So, if BD takes place twice on the same day [in April] do you count it only once.....or twice?

7. ## Re: Count Number of times a month appears in a range of dates

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!!!

8. ## Re: Count Number of times a month appears in a range of dates

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.

9. ## Re: Count Number of times a month appears in a range of dates

Originally Posted by ryan.katy
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•