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)*(Re sults!$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

=SUMPRODUCT((Results!$A$2:$A$5992=Analysis!A2)*(MO NTH(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


" wrote:

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)*(Re sults!$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

to include year .....

=SUMPRODUCT((Results!$A$2:$A$5992=Analysis!A2)*(MO NTH(Results!$B$2:$B$5992)=Analysis!$B$1)*(YEAR(Res ults!$B$2:$B$5992)=Analysis!$B$2)*(Results!$B$2:$B $5992<""))

B2=2007


OR


=SUMPRODUCT((Results!$A$2:$A$5992=Analysis!A2)*(TE XT(Results!$B$2:$B$5992,"mmyy")=Analysis!$B$3)*(Re sults!$B$2:$B$5992<""))

B3="0107" (text field)

"Toppers" wrote:

=SUMPRODUCT((Results!$A$2:$A$5992=Analysis!A2)*(MO NTH(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


" wrote:

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)*(Re sults!$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

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.