0

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

enter image description here

The date values have the following characteristics:

enter image description here

In my second tab I have:

enter image description here

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?

0

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.