# Countif for dates with wildcards

1. ## 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

2. ## Re: Countif for dates with wildcards

=SUMPRODUCT(--(C1:C50<>""),--(MONTH(C1:C50)=1))

I wouldn't use the whole range with sumproduct as it will evaluate all cells in the range which makes it inefficient.

3. ## Re: Countif for dates with wildcards

Upload sample file please. Without looking at actual spreadsheet, it's impossible to tell what's actually stored in a cell.

It could be either DateTime value (with underlying decimal/double value) or string/text stored in cell.

Provided that cells actually store datetime values. You can use COUNTIFS to count values in a given month.
=COUNTIFS(Complaints!C:C,">="&DATE(2017,1,1),Complaints!C:C,"<"&DATE(2017,2,1))

As for SUMPRODUCT... try.
=SUMPRODUCT((MONTH(Complaints!C:C)=1)*1)

However, you should take care to use only the range containing values for SUMPRODUCT. Unlike SUMIF(s),COUNTIF(s) family of function, SUMPRODUCT will iterate over entire range specified, and create unnecessary overhead. (Ex. Use C1:C200 if data is in that range, instead of entire column C:C).

4. ## Re: Countif for dates with wildcards

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).

5. ## Re: Countif for dates with wildcards

Thanks

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

seems to be doing the trick - much appreciated

6. ## Re: Countif for dates with wildcards

There are currently 1 users browsing this thread. (0 members and 1 guests)