CountIF by Month in current year

[chatnet26]

Dear Guru,

I am a memeber of this forum for more than a year, and currently assign into a new assignment which dealing an excel file everyday. We have one excel file for moniroting of action items generated by the management after the study. As since there were around 2,500+ rows has been generated since in the beginning of 1990's till todate. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this month out of the total numbers of action items.

Is it possible to use the COUNTIF formula to count the number of items in Col C, where Col B contains a date?

2-Jan-13 Closed
2-Jan-13 Closed
5-Jan-13 Closed
19-Feb-13 Closed
16-Feb-13 Closed
22-Feb-13 Closed
2-Mar-14 Closed
5-Mar-14 Closed
8-Mar-14 Closed
10-Mar-14 Closed
15-Mar-14 Closed


Appreciated your valuable response.

Best Regards,

[:) Sixthsense :)]

Try like this...

=SUMPRODUCT((TEXT(A1:A11,"MMMYYYY")=TEXT(TODAY(),"MMMYYYY"))*(B1:B11="Closed"))

Or

=COUNTIFS(A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),A:A,"<="&EOMONTH(TODAY(),0),B:B,"Closed")

[chatnet26]

Dear Sir,

Appreciated your quick response and really it is a perfect method.

I share it to the team and they are so happy.

Can ask another favor on how to calculate the YTD Closed!

Many thanks

Best Regards,

[:) Sixthsense :)]

Not sure what do you mean by YTD Can you please show some sample data in excel with expected result for better understanding.

[chatnet26]

Dear Sir,

I tried to attached the excel file, however I could not able to upload the file as I think it is not yet been enable. And the second thing is I want to count the number of year that has been closed of the current year. As you see the example there were 5 items has been closed for the year of 2014 while there were 4 items has been closed for the month of March 2014.


TYPE MTD Closing Date Status
Project 2-Jan-13 Closed
Project 2-Jan-13 Closed
Project 5-Jan-13 Closed
Project 19-Feb-13 Closed
Project 16-Feb-13 Closed
Project 22-Feb-13 Closed
Process 2-Feb-14 Closed
Process 5-Mar-14 Closed
Process 8-Mar-14 Closed
Process 10-Mar-14 Closed
Process 15-Mar-14 Closed

=COUNTIFS(A4:A14,"Process",B4:B14,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),B4:B14,"<="&EOMONTH(TODAY(),0),C4:C14,"Closed")

I have used your formula to count MTD closed and it is perfectly match to my requirements.

Best Regards,

[:) Sixthsense :)]

It better you use the sumproduct() formula which I provided earlier instead of the countifs() because it is easier for you to set the conditions.

SUMPRODUCT((TEXT(A1:A11,"MMMYYYY")=TEXT(TODAY(),"MMMYYYY"))*(B1:B11="Closed"))

In the above remove the MMM and apply it with your data set and confirm

[chatnet26]

Dear Sir,

Again many thanks to your quick response. Appreciated so much with your kind assistance. The method you've provided earlier is working great. How can I include in this logic to include the count the specific type let say "Process". So in this case there were 5 closed for this current year and while there were 4 closed for the month of March 2014.

Apologize for this as I supposed to mentioned earlier in my post.

Best Regards,

[:) Sixthsense :)]

=SUMPRODUCT((TEXT(A1:A11,"YYYY")=TEXT(TODAY(),"YYYY"))*(B1:B11="Closed"))

[chatnet26]

Hi Sixthsense,

Many thanks to your valuable input and really appreciated so much. Again many thanks...

God Bless.

Best Regards,

[oeldere]

And how about a solution with an pivot table?

[:) Sixthsense :)]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.