Reporting by month and year

[quetzalc0atl]

Hello All,

I am currently reporting on the number of interactions that are
recorded in our contact centre every week.

I have a work sheet called Results which holds information along the
lines of

Col A Col B Col C Col D
Enquiry Type | Date | Time | UserID

On my analysis worksheet I am trying to determine the number of
enquiry types per day (over a particular working week of 5 days) using
a table similar to:-

Col A Col B | Col C ......
Enquiry Type | 01/01/2007 | 02/01/2007 ......

N.B dates are in UK format dd/mm/yyyy

I am currently using the formula to report on the number of enquiries
for a particular day.
=SUMPRODUCT((Results!$A$2:$A$5992=Analysis!A2)*(Results!$B$2:$B
$5992=Analysis!$B$1))

What I would like to do is to try and expand this formula so that I
can report on the type of enquiries over a particular month. Is there
anyway of applying a wildcard to perhaps search for number of
enquiries in 01/2007 (Jan 2007)?

The easier solution to this problem is just to total up the number of
enquiries over the 4/5 weeks of the month. But I would like to see if
this is possible.

Any help would be greatly appreciated.

Kind Regards,

Clive

 

[Toppers]

=SUMPRODUCT((Results!$A$2:$A$5992=Analysis!A2)*(MONTH(Results!$B$2:$B$5992)=Analysis!$B$1)*(Results!$B$2:$B$5992<>""))

where B1 contains the required month e.g 1 for January

HTH

 

[Toppers]

to include year .....

=SUMPRODUCT((Results!$A$2:$A$5992=Analysis!A2)*(MONTH(Results!$B$2:$B$5992)=Analysis!$B$1)*(YEAR(Results!$B$2:$B$5992)=Analysis!$B$2)*(Results!$B$2:$B$5992<>""))

B2=2007


OR


=SUMPRODUCT((Results!$A$2:$A$5992=Analysis!A2)*(TEXT(Results!$B$2:$B$5992,"mmyy")=Analysis!$B$3)*(Results!$B$2:$B$5992<>""))

B3="0107" (text field)

 

[quetzalc0atl]

Hello Toppers,

Thanks for that I see what you are trying to do. However when I tried
it the formula returned a value of 0.