Using COUNTIF and AND to screen data

[ConanLloyd]

Greetings, how's this for a first post? :-)

I have to produce daily reports for a helpdesk and the one I'm stuck on at the moment is a report on ticket volume from email by week. I have included my file and would like some help with automating it a bit.

What I currently have is:

Please Login or Register  to view this content.

'Raw Data'!BH has the week numbers in it. 'Raw Data'!S4 is the submit date for the ticket. 'HD Mailbox Working Data'!A6 has the week beginning date for the week number. 'HD Mailbox Working Data'!B6 has the week number for comparison. 'Raw Data'!AQ has the method of ticket creation.

What I need is a count of the tickets created in a given week that came in by email. I want it to compare the submit date to the week beginning date to ensure it is a ticket from this year, then look for it to be created through email.

I had it at one point and then tried to add the second condition and can't seem to get anything to work.

Edited to add link to file since it won't let me upload it here: https://files.me.com/conan.lloyd/8kpavt

[Cheeky Charlie]

Try using countifs() - not countif() (if you are using 2007).

You can't use COUNTIF and AND.

I think the equation you need will look a bit like:
=countifs('Raw Data'!S:S,">="&A4,'Raw Data'!S:S,"<"&A5,'Raw Data'!AQ:AQ,"email")
but I can't test it...

[ConanLloyd]

Try using countifs() - not countif() (if you are using 2007).

I wish! I have asked for an upgrade but am stuck with 2003 for now.

BTW, thanks for the quick response though!

[Cheeky Charlie]

OK
1 - take it out of your user profile.
2 - I would use a pivot table:
Select the whole table in the raw data sheet - don't go down beyond the end of the data as you have in column BH.
Data->Pivot...
Finish
Drag 'submit date' to the 'row field' area
Right click one of the dates that appears - group and show - group
Highlight days, unhighlight months
Put '7' into the number of days box
OK
Drag 'Reported Source2' to the page field area (at the top) - this is equivalent to column AQ - 'Reported Source' is column Q
Click the drop down and select 'email'
Drag Ticket # to data area

I get:
29/08/2010 - 04/09/2010 170
05/09/2010 - 11/09/2010 192
12/09/2010 - 18/09/2010 295
19/09/2010 - 25/09/2010 332
26/09/2010 - 02/10/2010 396
03/10/2010 - 09/10/2010 332
10/10/2010 - 16/10/2010 340
17/10/2010 - 23/10/2010 382
24/10/2010 - 30/10/2010 413
31/10/2010 - 06/11/2010 327
07/11/2010 - 13/11/2010 264
14/11/2010 - 20/11/2010 447
21/11/2010 - 27/11/2010 195
28/11/2010 - 04/12/2010 321
05/12/2010 - 08/12/2010 187
Grand Total 4593

[ConanLloyd]

OK
1 - take it out of your user profile.

Thanks for another quick response!

Sorry, I have 2007 at home and 2003 at work. I'll take it out though since most of my questions will be due to work projects.

Trying your solution now.

Editied to add: Thanks a ton! Not only did that fix that issue, but it provided a template for more of my "Dashboard reports" Thanks!