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

You seem not to have sent the part of the message where you told us what
formula was giving you trouble, what the data values are, what result you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in message
...
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

On Nov 13, 10:57*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told us what
formula was giving you trouble, what the data values are, what result you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in ...

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

Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

It's always easiest if you tell us what you've tried and what went wrong
with that. Then we can help you to cure the problem.
You say:
"I havent't managed to find one formula that could provide any useful
results.",
but I guess that if you had done you wouldn't have asked the question.
Tell us what you've tried and in what way it went wrong, then we can tell
you how to improve it.

Why not
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) ?

Adjust the ranges to suit, and adjust the = and < depending on whether you
want your limits to be inclusive or exclusive.
--
David Biddulph

"Pedro Dias Ferreira" wrote in message
...
On Nov 13, 10:57 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told us what
formula was giving you trouble, what the data values are, what result
you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

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

Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

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
.

On Nov 13, 11:48*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
It's always easiest if you tell us what you've tried and what went wrong
with that. *Then we can help you to cure the problem.
You say:
"I havent't managed to find one formula that could provide any useful
results.",
but I guess that if you had done you wouldn't have asked the question.
Tell us what you've tried and in what way it went wrong, then we can tell
you how to improve it.

Why not
*=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) ?

Adjust the ranges to suit, and adjust the = and < depending on whether you
want your limits to be inclusive or exclusive.
--
David Biddulph

"Pedro Dias Ferreira" wrote in ...

On Nov 13, 10:57 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told us what
formula was giving you trouble, what the data values are, what result
you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

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
Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

I started digging several of the old spreadsheets :)
One of the attemps was:
=SUMPRODUCT((AD4:AD33=B1)*(AD4:AD33<=B2)*(Live!$R $4:$R$33=B14)) which
delivered a N/A
the final bit refers to the place where the call outcome is and b14 a
specific call outcome

As for the formula u proposed David, what does the A1:A10 at the end
refers to?

Thanks a lot for your help David. Any help u can give is great!!!

Remind us what the *1 does in that formula, Nick?
And I think you've jumped between D7:D11=1 and D7:D11=E7
--
David Biddulph

"Nicholas Perkins" wrote in
message ...
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
.

A1 to A10 was where you told us the "call outcome" was. I assumed that you
wanted to add up the values in that column which were in the rows where
column B was between your specified dates. If instead of that you are
trying to count how many rows have column A equal to a value in D1 and also
have column B in your date range, change
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) to
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),--(A1:A10=D$1))
--
David Biddulph

"Pedro Dias Ferreira" wrote in message
...
On Nov 13, 11:48 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
It's always easiest if you tell us what you've tried and what went wrong
with that. Then we can help you to cure the problem.
You say:
"I havent't managed to find one formula that could provide any useful
results.",
but I guess that if you had done you wouldn't have asked the question.
Tell us what you've tried and in what way it went wrong, then we can tell
you how to improve it.

Why not
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) ?

Adjust the ranges to suit, and adjust the = and < depending on whether
you
want your limits to be inclusive or exclusive.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

On Nov 13, 10:57 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told us
what
formula was giving you trouble, what the data values are, what result
you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

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
Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

I started digging several of the old spreadsheets :)
One of the attemps was:
=SUMPRODUCT((AD4:AD33=B1)*(AD4:AD33<=B2)*(Live!$R $4:$R$33=B14)) which
delivered a N/A
the final bit refers to the place where the call outcome is and b14 a
specific call outcome

As for the formula u proposed David, what does the A1:A10 at the end
refers to?

Thanks a lot for your help David. Any help u can give is great!!!

On Nov 13, 12:15*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
A1 to A10 was where you told us the "call outcome" was. *I assumed that you
wanted to add up the values in that column which were in the rows where
column B was between your specified dates. *If instead of that you are
trying to count how many rows have column A equal to a value in D1 and also
have column B in your date range, change
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) to
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),--(A1:A10=D$1))
--
David Biddulph

"Pedro Dias Ferreira" wrote in ...
On Nov 13, 11:48 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



It's always easiest if you tell us what you've tried and what went wrong
with that. Then we can help you to cure the problem.
You say:
"I havent't managed to find one formula that could provide any useful
results.",
but I guess that if you had done you wouldn't have asked the question.
Tell us what you've tried and in what way it went wrong, then we can tell
you how to improve it.

Why not
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) ?

Adjust the ranges to suit, and adjust the = and < depending on whether
you
want your limits to be inclusive or exclusive.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

On Nov 13, 10:57 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told us
what
formula was giving you trouble, what the data values are, what result
you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

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
Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

I started digging several of the old spreadsheets :)
One of the attemps was:
=SUMPRODUCT((AD4:AD33=B1)*(AD4:AD33<=B2)*(Live!$R $4:$R$33=B14)) which
delivered a N/A
the final bit refers to the place where the call outcome is and b14 a
specific call outcome

As for the formula u proposed David, what does the A1:A10 at the end
refers to?

Thanks a lot for your help David. Any help u can give is great!!!

I am getting frustrated :(
u guys are great and I think both formulas should work but now it
returns 0!!
can it have to do with the fact that i am using dates?
thanks!!

On Nov 13, 12:22*pm, Pedro Dias Ferreira
wrote:
On Nov 13, 12:15*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



A1 to A10 was where you told us the "call outcome" was. *I assumed that you
wanted to add up the values in that column which were in the rows where
column B was between your specified dates. *If instead of that you are
trying to count how many rows have column A equal to a value in D1 and also
have column B in your date range, change
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) to
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),--(A1:A10=D$1))
--
David Biddulph

"Pedro Dias Ferreira" wrote in ...
On Nov 13, 11:48 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

It's always easiest if you tell us what you've tried and what went wrong
with that. Then we can help you to cure the problem.
You say:
"I havent't managed to find one formula that could provide any useful
results.",
but I guess that if you had done you wouldn't have asked the question..
Tell us what you've tried and in what way it went wrong, then we can tell
you how to improve it.

Why not
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) ?

Adjust the ranges to suit, and adjust the = and < depending on whether
you
want your limits to be inclusive or exclusive.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

On Nov 13, 10:57 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told us
what
formula was giving you trouble, what the data values are, what result
you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

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
Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

I started digging several of the old spreadsheets :)
One of the attemps was:
=SUMPRODUCT((AD4:AD33=B1)*(AD4:AD33<=B2)*(Live!$R $4:$R$33=B14)) which
delivered a N/A
the final bit refers to the place where the call outcome is and b14 a
specific call outcome

As for the formula u proposed David, what does the A1:A10 at the end
refers to?

Thanks a lot for your help David. Any help u can give is great!!!

I am getting frustrated :(
u guys are great and I think both formulas should work but now it
returns 0!!
can it have to do with the fact that i am using dates?
thanks!!

and excel2003...

If the "dates" aren't really Excel dates, but are text, then the formula
won't work and you'll have to convert them from text to real dates. If
you've got "dates" in column B, see what =ISTEXT(B2) and =ISNUMBER(B2) tell
you.
--
David Biddulph

"Pedro Dias Ferreira" wrote in message
...
On Nov 13, 12:15 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
A1 to A10 was where you told us the "call outcome" was. I assumed that you
wanted to add up the values in that column which were in the rows where
column B was between your specified dates. If instead of that you are
trying to count how many rows have column A equal to a value in D1 and
also
have column B in your date range, change
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) to
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),--(A1:A10=D$1))
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...
On Nov 13, 11:48 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



It's always easiest if you tell us what you've tried and what went wrong
with that. Then we can help you to cure the problem.
You say:
"I havent't managed to find one formula that could provide any useful
results.",
but I guess that if you had done you wouldn't have asked the question.
Tell us what you've tried and in what way it went wrong, then we can
tell
you how to improve it.

Why not
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) ?

Adjust the ranges to suit, and adjust the = and < depending on whether
you
want your limits to be inclusive or exclusive.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

On Nov 13, 10:57 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told us
what
formula was giving you trouble, what the data values are, what
result
you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

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
Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

I started digging several of the old spreadsheets :)
One of the attemps was:
=SUMPRODUCT((AD4:AD33=B1)*(AD4:AD33<=B2)*(Live!$R $4:$R$33=B14)) which
delivered a N/A
the final bit refers to the place where the call outcome is and b14 a
specific call outcome

As for the formula u proposed David, what does the A1:A10 at the end
refers to?

Thanks a lot for your help David. Any help u can give is great!!!

I am getting frustrated :(
u guys are great and I think both formulas should work but now it
returns 0!!
can it have to do with the fact that i am using dates?
thanks!!

On Nov 13, 12:36*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
If the "dates" aren't really Excel dates, but are text, then the formula
won't work and you'll have to convert them from text to real dates. *If
you've got "dates" in column B, see what =ISTEXT(B2) and =ISNUMBER(B2) tell
you.
--
David Biddulph

"Pedro Dias Ferreira" wrote in ...
On Nov 13, 12:15 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



A1 to A10 was where you told us the "call outcome" was. I assumed that you
wanted to add up the values in that column which were in the rows where
column B was between your specified dates. If instead of that you are
trying to count how many rows have column A equal to a value in D1 and
also
have column B in your date range, change
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) to
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),--(A1:A10=D$1))
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...
On Nov 13, 11:48 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

It's always easiest if you tell us what you've tried and what went wrong
with that. Then we can help you to cure the problem.
You say:
"I havent't managed to find one formula that could provide any useful
results.",
but I guess that if you had done you wouldn't have asked the question..
Tell us what you've tried and in what way it went wrong, then we can
tell
you how to improve it.

Why not
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) ?

Adjust the ranges to suit, and adjust the = and < depending on whether
you
want your limits to be inclusive or exclusive.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

On Nov 13, 10:57 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told us
what
formula was giving you trouble, what the data values are, what
result
you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

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
Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

I started digging several of the old spreadsheets :)
One of the attemps was:
=SUMPRODUCT((AD4:AD33=B1)*(AD4:AD33<=B2)*(Live!$R $4:$R$33=B14)) which
delivered a N/A
the final bit refers to the place where the call outcome is and b14 a
specific call outcome

As for the formula u proposed David, what does the A1:A10 at the end
refers to?

Thanks a lot for your help David. Any help u can give is great!!!

I am getting frustrated :(
u guys are great and I think both formulas should work but now it
returns 0!!
can it have to do with the fact that i am using dates?
thanks!!

They say true for ISNUMBER and False for ISTEXT

You'll need to tell us EXACTLY what formula you are using that gives a
result of zero, and tell us the contents of at least enough of the relevant
cells that convince you that you should be getting a non-zero value.

[note that for some reason the quote indications are not being added
correctly by OE to your message below, but this time I'm not going to add
them by hand. Hopefully it's clear enough who said what.]
--
David Biddulph

"Pedro Dias Ferreira" wrote in message
...
On Nov 13, 12:36 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
If the "dates" aren't really Excel dates, but are text, then the formula
won't work and you'll have to convert them from text to real dates. If
you've got "dates" in column B, see what =ISTEXT(B2) and =ISNUMBER(B2)
tell
you.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...
On Nov 13, 12:15 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



A1 to A10 was where you told us the "call outcome" was. I assumed that
you
wanted to add up the values in that column which were in the rows where
column B was between your specified dates. If instead of that you are
trying to count how many rows have column A equal to a value in D1 and
also
have column B in your date range, change
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) to
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),--(A1:A10=D$1))
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...
On Nov 13, 11:48 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

It's always easiest if you tell us what you've tried and what went
wrong
with that. Then we can help you to cure the problem.
You say:
"I havent't managed to find one formula that could provide any useful
results.",
but I guess that if you had done you wouldn't have asked the question.
Tell us what you've tried and in what way it went wrong, then we can
tell
you how to improve it.

Why not
=SUMPRODUCT(--(B1:B10=C$1),--(B1:B10<C$2),A1:A10) ?

Adjust the ranges to suit, and adjust the = and < depending on
whether
you
want your limits to be inclusive or exclusive.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

On Nov 13, 10:57 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
You seem not to have sent the part of the message where you told
us
what
formula was giving you trouble, what the data values are, what
result
you
got, and what result you expected.
--
David Biddulph

"Pedro Dias Ferreira" wrote in
...

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
Well,

I havent't managed to find one formula that could provide any useful
results. Countif can't be used because of the limit on the number of
criteria and I don't seem to work well with sumproduct
Data is organised like this:

Column A - Call Outcome
Column B - Date of call

Cells c1 - start of week 1
cell c2 - end of week 1
cell d1 - the call outcome i want to count

is this useful?

thanks a lot

I started digging several of the old spreadsheets :)
One of the attemps was:
=SUMPRODUCT((AD4:AD33=B1)*(AD4:AD33<=B2)*(Live!$R $4:$R$33=B14)) which
delivered a N/A
the final bit refers to the place where the call outcome is and b14 a
specific call outcome

As for the formula u proposed David, what does the A1:A10 at the end
refers to?

Thanks a lot for your help David. Any help u can give is great!!!

I am getting frustrated :(
u guys are great and I think both formulas should work but now it
returns 0!!
can it have to do with the fact that i am using dates?
thanks!!

They say true for ISNUMBER and False for ISTEXT