Formula needed for COUNTIF in current month

**atomickitty** · 08-01-2012, 11:33 AM

I have a spreadsheet that tracks all the projects we have worked on over the last 5 years up until today. Column H lists the start date of each of the projects in MM/DD/YYYY format.

I want to give a count of how many projects we are working on in the current month based on the start date in Column H. Can I use COUNTIF for this and what is the formula to use?

**daddylonglegs** · 08-01-2012, 11:42 AM

Try using SUMPRODUCT like this

=SUMPRODUCT((TEXT(H2:H100,"mmm-yy")=TEXT(TODAY(),"mmm-yy))+0)

adjust range as required

**atomickitty** · 08-01-2012, 11:49 AM

That worked perfectly but only if the date in column H is today's date. How do I get it to sum for any date in the current month?

**daddylonglegs** · 08-01-2012, 01:47 PM

It should count any date in H2:H100 that is in the current month, do you want it to include previous dates for projects that are still going in this month, how do you know if a project is no longer active, do you have an end date column?

**atomickitty** · 08-02-2012, 11:36 AM

I do have an end date column but I just want to know which projects were started in the current month.

And I checked the formula today (Aug 2) and it is returning 0 projects. Yesterday it returned 2 projects, but only because I had entered the start date as August 1.

**daddylonglegs** · 08-02-2012, 03:00 PM

The formula should count all dates in the current month, i.e. all August 2012 dates at the moment. I noticed I left a quote out at the end though, so it should be like this

=SUMPRODUCT((TEXT(H2:H100,"mmm-yy")=TEXT(TODAY(),"mmm-yy"))+0)

Perhaps you corrected my error incorrectly?

That converts todays date into a text string of the month and year, i.e. Aug-12 and then counts how many dates in H2:H100 also convert to the same string, hence counting all in the current month