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:

=COUNTIF('Raw Data'!BH:BH,(AND('Raw Data'!S4>='HD Mailbox Working Data'!A6,'Raw Data'!BH4='HD Mailbox Working Data'!B6,'Raw Data'!AQ="Email")))

'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