Countif for dates with wildcards

Hi All,

Looking for a bit of help please, i have a list of issues/complaints from January 2017 to date. They are currently just a list in a spreadsheet i want to count how many occur in each month.

The date format is 03/01/2017 09:00:00, (only 03/01/2017 is visibale in the cell, its only when you click on a specific example that you see the time is also included) I've tried doing

=COUNTIF(Complaints!C:C,"*/01/2017*") but this didn't work,it returns a zero and i know this is incorrect. I've been reading online and some similar questions have been advised to use the sumproduct function but this seems to be in more complex questions then mine - i tried the below but the #Value error is returned.

=SUMPRODUCT(MONTH(Complaints!C:C)=1)

What am i missing?

Any pointers much appreciated

How depends on if the dates are dates or text. From your post it sounds like they are dates.

If thats the case, be aware that dates arent something like "03/01/2017" to Excel. All date and time values in Excel are actually serial numbers representing the date in a decimal number representation with whole numbers representing the day, month year as the number of days from Jan 1, 1900 and the decimal portion representing the time of day 0= 12 am, .99 (repeating) = 11:59:59pm.

So you could figure out the whole number portion of the serial number and use that as a criteria.

However, it is likely easier to use SUMPRODUCT with the MONTHS function as CK76 shows. Id further recommend only referencing the data range you are using instead of the full column (ex: C2:C10 instead of C:C).

Thanks

=COUNTIFS(Complaints!C:C,">="&DATE(2017,1,1),Complaints!C:C,"<"&DATE(2017,2,1))

seems to be doing the trick - much appreciated