COUNTIFS with dates

Question

I'm trying to get the number of support tickets for each month. In my first tab "Tickets" I have:

The date values have the following characteristics:

In my second tab I have:

I want to know how much tickets for each month. I've tried this:

= COUNTIFS(Tickets!B2:B196;">=1/06/17";Tickets!B2:B196;"<=31/06/17")

But that results in 0, which is not correct. Can you help me?

Answer 1

There are a few ways of accomplishing this, however the easiest would be as follows:

=COUNTIFS(Tickets!B2:B196;">=DATEVALUE(1/06/17)";Tickets!B2:B196;"<=DATEVALUE(31/06/17)")

This will convert the dates to a date serial, which will match the data format within each cell.

Alternately, you could use either a helper column with:

=MONTH([cell])

You would then do the COUNTIF on the helper column.

As a third option, you could use an array formula similar to the following:

=IF(MONTH([range])=[number 1-12],COUNT([range]))

Press ctrl+shift+enter to activate

The important thing to remember is that dates are stored as a serial number since 01/01/1900 including 02/29/1900, and MONTH returns an integer.