countifs; criteria: given year and month.

[ezechiel]

Environment: aftersales ticket follow up and reporting.

I have a sheet with a whole bunch of tickets with the following characteristics:

ticket nr | open date | due date | close date | country | status ...


Now I have a problem:

1. In another sheet, I have a list of each month/year
jan 2009
...
april 2011

In the cell next to the month/year, I want to put the number of occurences
of tickets opened (or closed) during that month.

This is what I tried:

=COUNTIFS(Tickets!$F$2:$F$400;YEAR=2009;Tickets!$F$2:$F$400;MONTH="October")

I tried also to replace october with 10 and without quotes but it does not work neither... returning 0 every time.

(an additional question would be to have the same per week (with WEEKNUM?), but without mixing up week 1 of 2010 and week 1 of 2011 ^^ )

And please give me an answer with COUNTIFS function. I never understood a yota of the SUMPRODUCT funtion... Or if you can explain it very well, you can try

2.
I would also like to have the average duration of a ticket with an excel formula.
(or VBA that puts the value in a cell).

Thanks in advance for your help.
Excel is driving me crazy sometimes..

 

[FormR]

=+COUNTIFS($D$2:$D$330;"*"&J5&"*";$E$2:$E$415;">="&H6;$E$2:$E$415;"<"&H7)

Hi, all your ranges need to be the same size, for example:

=+COUNTIFS($D$2:$D$415;"*"&J5&"*";$E$2:$E$415;">="&H6;$E$2:$E$415;"<"&H7)

 

[weepingpea]

Hi, all your ranges need to be the same size, for example:

=+COUNTIFS($D$2:$D$415;"*"&J5&"*";$E$2:$E$415;">="&H6;$E$2:$E$415;"<"&H7)

That is amazing! Thank you - it worked!