Hi,

I have a list of Start Dates starting in A2 and running down to A35. I also
have a list of End Date strting in B2 and running down to B35 (Note: Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end date
and list it as the number approved, rejected in 5 days or less : 6 to 10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Hi

On the same sheet, in cell F1 enter 5, G1 10, H1 20 and in I1 100 (or a
large number of days that will not be exceeded)
In E2 enter Accepted, and in E3 enter Rejected
en cell F2 enter
=SUMPRODUCT(($B$2:$B$6<"")*
($B$2:$B$6-$A$2:$A$6E$1)*
($B$2:$B$6-$A$2:$A$6<=F$1)*
($C$2:$C$6=$E2))

and copy across through cells G2:I2
Copy F2:I2 down to F3

--
Regards

Roger Govier


"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to A35. I
also
have a list of End Date strting in B2 and running down to B35 (Note:
Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end
date
and list it as the number approved, rejected in 5 days or less : 6 to
10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 -
20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but
i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to A35. I
also
have a list of End Date strting in B2 and running down to B35 (Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end date
and list it as the number approved, rejected in 5 days or less : 6 to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Roger / Bob,

Thankyou for your assistance, I am a long way closer to were I want to be.

Rogier,

I have 1 question about you solution. Everything appears to work perfectly
apart from 1 bit. Were the Start and End dates are the same, the value is 0.
The formula doesnt appear to account for this and I dont seem to be able to
adapt it to do so. So it break the numbers down from 1 - 5 days, 6 - 10 days
and so on ok, but it wont allow for the open and close to be the same day.

Rgds Paul

"Roger Govier" wrote:

Hi

On the same sheet, in cell F1 enter 5, G1 10, H1 20 and in I1 100 (or a
large number of days that will not be exceeded)
In E2 enter Accepted, and in E3 enter Rejected
en cell F2 enter
=SUMPRODUCT(($B$2:$B$6<"")*
($B$2:$B$6-$A$2:$A$6E$1)*
($B$2:$B$6-$A$2:$A$6<=F$1)*
($C$2:$C$6=$E2))

and copy across through cells G2:I2
Copy F2:I2 down to F3

--
Regards

Roger Govier


"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to A35. I
also
have a list of End Date strting in B2 and running down to B35 (Note:
Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end
date
and list it as the number approved, rejected in 5 days or less : 6 to
10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 -
20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but
i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

I figured it out, woohooo!

If I put -1 into cell e1 it performs the calculation on the dates that are
equal as well.

thanks

Rgds Paul

"Bob Phillips" wrote:

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to A35. I
also
have a list of End Date strting in B2 and running down to B35 (Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end date
and list it as the number approved, rejected in 5 days or less : 6 to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Hi Paul

Well figured out!!
Many thanks for letting us know you resolved the problem.

--
Regards

Roger Govier


"phocused" wrote in message
...
I figured it out, woohooo!

If I put -1 into cell e1 it performs the calculation on the dates that
are
equal as well.

thanks

Rgds Paul

"Bob Phillips" wrote:

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to
A35. I
also
have a list of End Date strting in B2 and running down to B35
(Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and
end date
and list it as the number approved, rejected in 5 days or less : 6
to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days
10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets,
but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Roger,

I figured it out but am still confused to how it worked.

Is there any way I can ask a favour, is it possible you could take me
through what is happening in the calculation?

I understand that it is taking the product of the array and multiplying it
by the product of the second array but i just dont see how it arrives at 1 or
2 or whatever :-)

Rgds Paul

PS i will understand if this is to long and arduos.


"Roger Govier" wrote:

Hi Paul

Well figured out!!
Many thanks for letting us know you resolved the problem.

--
Regards

Roger Govier


"phocused" wrote in message
...
I figured it out, woohooo!

If I put -1 into cell e1 it performs the calculation on the dates that
are
equal as well.

thanks

Rgds Paul

"Bob Phillips" wrote:

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to
A35. I
also
have a list of End Date strting in B2 and running down to B35
(Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and
end date
and list it as the number approved, rejected in 5 days or less : 6
to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days
10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets,
but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Hi Paul

=SUMPRODUCT(($B$2:$B$6<"")*
($B$2:$B$6-$A$2:$A$6E$1)*
($B$2:$B$6-$A$2:$A$6<=F$1)*
($C$2:$C$6=$E2))

The first term inside the Sumproduct, is testing whether the item is
Closed - this returns True or False
The next tests whether the Interval between the two dates is Greater
than the First date band, which I had left blank to start at 0, and you
amended to -1 to pick up 0 - again True or False
The Next term tests whether the interval is Less than or Equal to the
Second Date band (5 days) - again True or False
Finally, the last check is whether column C contains "Approved" (cell
E2) - again True or False

Because these Terms are being multiplied together, this coerces the
Trues' to 1's and False's to 0's

so 1 x 1 x 1 x 1 =1
but 0 x 1 x 1 x 1 = 0
and a 0 for any of the 4 will return 0, so only anything which satisfies
all conditions will return a 1.

Sumproduct then adds these together to give the totals for each group.

Bob has a great treatise on Sumproduct on his site at
http://xldynamic.com/source/xld.SUMPRODUCT
--
Regards

Roger Govier


"phocused" wrote in message
...
Roger,

I figured it out but am still confused to how it worked.

Is there any way I can ask a favour, is it possible you could take me
through what is happening in the calculation?

I understand that it is taking the product of the array and
multiplying it
by the product of the second array but i just dont see how it arrives
at 1 or
2 or whatever :-)

Rgds Paul

PS i will understand if this is to long and arduos.


"Roger Govier" wrote:

Hi Paul

Well figured out!!
Many thanks for letting us know you resolved the problem.

--
Regards

Roger Govier


"phocused" wrote in message
...
I figured it out, woohooo!

If I put -1 into cell e1 it performs the calculation on the dates
that
are
equal as well.

thanks

Rgds Paul

"Bob Phillips" wrote:

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to
A35. I
also
have a list of End Date strting in B2 and running down to B35
(Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start
and
end date
and list it as the number approved, rejected in 5 days or less :
6
to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days
10 - 20
days 20@
Approved 3 2
10 0
Rejected 1
0
0 0

I can do this over several calculations using several
worksheets,
but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Ah, the cloud is lifted. Roger, now I get it. I was taking things at face
value. The product of the Serial Numbers of the dates times the array..... No
wonder I was getting no where.

True and False.

Excellent, thanks you for your patience.

Rgds Paul

"phocused" wrote:

Hi,

I have a list of Start Dates starting in A2 and running down to A35. I also
have a list of End Date strting in B2 and running down to B35 (Note: Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end date
and list it as the number approved, rejected in 5 days or less : 6 to 10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Hi Paul

You're more than welcome. Enjoy the wonders of Sumproduct!!!

--
Regards

Roger Govier


"phocused" wrote in message
...
Ah, the cloud is lifted. Roger, now I get it. I was taking things at
face
value. The product of the Serial Numbers of the dates times the
array..... No
wonder I was getting no where.

True and False.

Excellent, thanks you for your patience.

Rgds Paul

"phocused" wrote:

Hi,

I have a list of Start Dates starting in A2 and running down to A35.
I also
have a list of End Date strting in B2 and running down to B35 (Note:
Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end
date
and list it as the number approved, rejected in 5 days or less : 6 to
10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 -
20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but
i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul