countifs; criteria: given year and month.

E

ezechiel

New Member
Joined
Apr 19, 2011
Messages
7
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:
Code: 
=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..
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
try this

Code: 
=SUMPRODUCT(--(YEAR($F$2:$F$400)=2009),--(MONTH(F$2:$F$400)=10))

I don't have 2007 Excel so I couldn't test your formula. BTW it looks like you have an international excel version so replace the "," with ";"
 
Upvote 0
Re: countifs; criteria: given year,month + other

Hi,

thanks for your answer. It worked great! But if I want to put an additional criterium, it does not work anymore..
The additional filter is on the country.

Code: 
=SUMPRODUCT(--(YEAR(Tickets!$F$2:$F$400)=2009);--(MONTH(Tickets!$F$2:$F$400)=10);--(Tickets!$L2:$L400)="Vanuatu")

Anyone knows how to solve this?

Thanks ;)
 
Upvote 0
Oops, a little error in the code. But this doesn't change anything.
The result is 0..

Code: 
=SUMPRODUCT(--(YEAR(Tickets!$F$2:$F$400)=2011);--(MONTH(Tickets!$F$2:$F$400)=4);--(Tickets!$L2:$L400="Vanuatu"))

Thanks for helping me out.. This is kind of urgent and would save my ***
 
Upvote 0
If you have 1st of each month in A2 down [you can format to show just mmm-yy] then for your original query you can use this formula in C2 copied down

=COUNTIFS(Tickets!$F$2:$F$400;">="&A2;Tickets!$F$2:$F$400;"<="&EOMONTH(A2;0))

Then you can extend that with an extra condition for the country, e.g.

=COUNTIFS(Tickets!$F$2:$F$400;">="&A2;Tickets!$F$2:$F$400;"<="&EOMONTH(A2;0);Tickets!$L$2:$L$400;B2)

where B2 contains the country name

For counting by weeks you can do the same sort of thing - put the start date of each week in F2 down, for instance, then use this version of the first formula above

=COUNTIFS(Tickets!$F$2:$F$400;">="&F2;Tickets!$F$2:$F$400;"<"&F2+7)
 
Upvote 0
Houdini, you're a magician :)

Everything works as expected, just great!

I'll try now to do the same for overdue tickets:
The number of tickets per month (and per country) where the close date
is superior to the due date
 
Upvote 0
Hi again.

I'm stuck again with these formulas because they give me a different result:

Formula 1: Result: 21
=COUNTIFS(Tickets!$G$2:$G$500;">="&$B63;Tickets!$G$2:$G$500;"<="&EOMONTH($B63;0))
Click to expand...

Fromula 2: Result: 23
=SUMPRODUCT(--(YEAR(Tickets!$G$2:$G$477)=2010);--(MONTH(Tickets!$G$2:$G$477)=3))
Click to expand...

And here is the list:
I think the problem is because of the two tickets that have
2010-03-31 as date. Anyone knows how to solve this?
<TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=108><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 46.5pt" height=62><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 46.5pt; BORDER-TOP: #c4c4c4 0.5pt solid; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=62 width=108 align=right>2010-03-03 07:45</TD></TR><TR style="HEIGHT: 46.5pt" height=62><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 46.5pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=62 width=108 align=right>2010-03-03 20:31</TD></TR><TR style="HEIGHT: 46.5pt" height=62><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 46.5pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=62 width=108 align=right>2010-03-04 01:26</TD></TR><TR style="HEIGHT: 46.5pt" height=62><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 46.5pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=62 width=108 align=right>2010-03-06 21:47</TD></TR><TR style="HEIGHT: 57.75pt" height=77><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 57.75pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=77 width=108 align=right>2010-03-07 14:34</TD></TR><TR style="HEIGHT: 35.25pt" height=47><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 35.25pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=47 width=108 align=right>2010-03-08 09:49</TD></TR><TR style="HEIGHT: 57.75pt" height=77><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 57.75pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=77 width=108 align=right>2010-03-08 16:56</TD></TR><TR style="HEIGHT: 57.75pt" height=77><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 57.75pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=77 width=108 align=right>2010-03-11 14:41</TD></TR><TR style="HEIGHT: 69pt" height=92><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 69pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=92 width=108 align=right>2010-03-16 17:39</TD></TR><TR style="HEIGHT: 57.75pt" height=77><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 57.75pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=77 width=108 align=right>2010-03-16 22:43</TD></TR><TR style="HEIGHT: 35.25pt" height=47><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 35.25pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=47 width=108 align=right>2010-03-19 12:11</TD></TR><TR style="HEIGHT: 35.25pt" height=47><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 35.25pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=47 width=108 align=right>2010-03-19 12:22</TD></TR><TR style="HEIGHT: 35.25pt" height=47><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 35.25pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=47 width=108 align=right>2010-03-22 10:47</TD></TR><TR style="HEIGHT: 35.25pt" height=47><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 35.25pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=47 width=108 align=right>2010-03-22 10:56</TD></TR><TR style="HEIGHT: 35.25pt" height=47><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 35.25pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=47 width=108 align=right>2010-03-22 11:05</TD></TR><TR style="HEIGHT: 35.25pt" height=47><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 35.25pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=47 width=108 align=right>2010-03-22 11:10</TD></TR><TR style="HEIGHT: 46.5pt" height=62><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 46.5pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=62 width=108 align=right>2010-03-22 12:34</TD></TR><TR style="HEIGHT: 57.75pt" height=77><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 57.75pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=77 width=108 align=right>2010-03-22 17:32</TD></TR><TR style="HEIGHT: 57.75pt" height=77><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 57.75pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=77 width=108 align=right>2010-03-22 17:33</TD></TR><TR style="HEIGHT: 69pt" height=92><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 69pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=92 width=108 align=right>2010-03-27 12:33</TD></TR><TR style="HEIGHT: 46.5pt" height=62><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 46.5pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=62 width=108 align=right>2010-03-29 13:20</TD></TR><TR style="HEIGHT: 46.5pt" height=62><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 46.5pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=62 width=108 align=right>2010-03-31 18:13</TD></TR><TR style="HEIGHT: 46.5pt" height=62><TD style="BORDER-BOTTOM: #c4c4c4 0.5pt solid; BORDER-LEFT: #c4c4c4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 46.5pt; BORDER-TOP: #c4c4c4; BORDER-RIGHT: #c4c4c4 0.5pt solid" class=xl65 height=62 width=108 align=right>2010-03-31 19:57</TD></TR></TBODY></TABLE>
 
Upvote 0
Ok, the problem is really the 31st of the month.

Because when trying with this (using "before 1st of next month"), it works. But then I always have to add a row with the following month
so it is not as neat....

=COUNTIFS(Tickets!$G$2:$G$500;">="&$B63;Tickets!$G$2:$G$500;"<"&$B64)
Click to expand...

So if someone could find a solution with EOMONTH... :s
Thanks.
 
Upvote 0
Hi everyone. I found the solution to the problem.
Code: 
=COUNTIFS(Tickets!$G$2:$G$500;">="&$A63;Tickets!$G$2:$G$500;"<="&EDATE($A63;1);Tickets!$D$2:$D$500;"MINOR";Tickets!$O$2:$O$500;"y")
Using EOMONTH, it does not take anything in account after 31 march 00:00.
Maybe because here
Code: 
Tickets!$G$2:$G$500
, the date is in a date time format.

But using EDATE($CELL,1) -> counting one month from start date
It does take in account every cell with 31 march and beyond 00:00

Hope this will help someone. :D
 
Upvote 0
Hi everyone, I have a quick question on a similar note. I need to construct a Countifs Formula that 1) searches for a term in a column (Column D) 2) Only count terms from a certain year (Column E)

Based on what I have read here I made the following, but is it currently returning the #VALUE error. I was wondering if you might see what is wrong with it?

=+COUNTIFS($D$2:$D$330;"*"&J5&"*";$E$2:$E$415;">="&H6;$E$2:$E$415;"<"&H7)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Red over White
  • Solved
Filter by specific days, months and years
Replies
3
Views
291
Red over White
Red over White
T
  • Solved
Automatically generate dates for specific days of the week on any given month
Replies
6
Views
288
Peter_SSs
Peter_SSs
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
206
Tetra201
Tetra201
E
  • Solved
Month and Year in Excel Header Mac
2
Replies
13
Views
1K
Peter_SSs
Peter_SSs
W
  • Question Question
how to get individual items in a range to change when using slicers
Replies
1
Views
181
iliace
iliace

Forum statistics

Threads
1,224,527
Messages
6,179,334
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top