Hi Pedro,


This sounds like something that would be handled well by an Array formula.
Array formulas are entered by pressing Ctrl+Shift+Enter instead of just Enter.


The formula I used when testing this out looked like this:

=SUM((C7:C11=G7)*(C7:C11<=H7)*(D7:D11=1)*1)

C7:C11 is where you have your call dates, D7:D11 is the outcome. G7 is the
start date for the range and H7 is the end date for the range. E7 is the
call outcome I want to count.

When you press Ctrl+Shift+Enter, it will have curly braces around it like
this:
{=SUM((C7:C11=G7)*(C7:C11<=H7)*(D7:D11=E7)*1)}

You have to make sure that the columns with the call dates and outcomes are
the same size or else the array formula will not work.

It looks a little strange if you haven't used array formulas before, so look
up about array formulas to get a basic understanding.
(http://www.cpearson.com/excel/ArrayFormulas.aspx is an excellent site)

Give it a try and if you get stuck, drop me a line and I can help further.

Cheers,
Nick


--
Nicholas Perkins
Excel Analyst, Designer and Programmer
http://www.nicholasperkins.com/
is bad.com
(Please remove the spamisbad)



"Pedro Dias Ferreira" wrote:

Hi,

I have been battling a problem for the last couple of days and can't
seem to be able to solve it.

I have a column with data regarding a call centre. In one of the
columns I have the date of a phone call and in another one I have the
call outcome. I want to group this by week now, defining ranges of
dates and the outcome for the calls that were made then.

Have tried several different ways with sumproduct but the problem
seems to be in Excel 2003 having some problem with the dates. By the
way, I have the dates that define the ranges in seprate cells and
refer to those cells when defining ranges.

Any help would be more than appreciated

thanks

Pedro
.