Iv got the following code:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),$D$10:$D$5000)

P3 is date from
R3 is date to
col A is date
col B is name
col D is hours
That gives me a breakdown of hours for both clients.

I need to further brake the hours down to find out what hours the clients
used under 20 hours and over as well.

--
aac

"aac" wrote in message
...
Iv got the following code:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),$D$10:$D$5000)

P3 is date from
R3 is date to
col A is date
col B is name
col D is hours
That gives me a breakdown of hours for both clients.

I need to further brake the hours down to find out what hours the clients
used under 20 hours and over as well.

--
aac

Add another condition to your SUMPRODUCT formula:
--($D$10:$D$5000<20)
will only add hours which are <20, whilst
--($D$10:$D$500020)
will add hours greater than 20.

However, you should include the =20 case in one or the other, so use
--($D$10:$D$5000<20) and --($D$10:$D$5000=20)
or
--($D$10:$D$5000<=20) and --($D$10:$D$500020)

Hi Stephen,
Thanks for that. But its not quit there. At the moment it sum any single
value under 20 and over 20. I need it to sum under a running total of 20.
Give you more info, single entries could be anything from 5 minutes upwards.
So its a cumulative total. I do have a col E10 to E5000 which keeps a running
total.

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002)--($E$10:$E$5000<20)--($E$10:$E$5000=20),$D$10:$D$5000)
Have I added them in the right place?

--
aac


"Stephen" wrote:

"aac" wrote in message
...
Iv got the following code:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),$D$10:$D$5000)

P3 is date from
R3 is date to
col A is date
col B is name
col D is hours
That gives me a breakdown of hours for both clients.

I need to further brake the hours down to find out what hours the clients
used under 20 hours and over as well.

--
aac

Add another condition to your SUMPRODUCT formula:
--($D$10:$D$5000<20)
will only add hours which are <20, whilst
--($D$10:$D$500020)
will add hours greater than 20.

However, you should include the =20 case in one or the other, so use
--($D$10:$D$5000<20) and --($D$10:$D$5000=20)
or
--($D$10:$D$5000<=20) and --($D$10:$D$500020)

For what I thought you wanted, what I intended was two separate formulas,
one with a condition <20 and the other with a condition =20, like this:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),--($E$10:$E$5000<20),$D$10:$D$5000)

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),--($E$10:$E$5000=20),$D$10:$D$5000)

You had both conditions in the same formula - both cannot be true together!
Also, you had missed some commas.

But I'm afraid I'm baffled as to whether or not this is what you are trying
to calculate.

"aac" wrote in message
...
Hi Stephen,
Thanks for that. But its not quit there. At the moment it sum any single
value under 20 and over 20. I need it to sum under a running total of 20.
Give you more info, single entries could be anything from 5 minutes
upwards.
So its a cumulative total. I do have a col E10 to E5000 which keeps a
running
total.

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002)--($E$10:$E$5000<20)--($E$10:$E$5000=20),$D$10:$D$5000)
Have I added them in the right place?

--
aac


"Stephen" wrote:

"aac" wrote in message
...
Iv got the following code:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),$D$10:$D$5000)

P3 is date from
R3 is date to
col A is date
col B is name
col D is hours
That gives me a breakdown of hours for both clients.

I need to further brake the hours down to find out what hours the
clients
used under 20 hours and over as well.

--
aac

Add another condition to your SUMPRODUCT formula:
--($D$10:$D$5000<20)
will only add hours which are <20, whilst
--($D$10:$D$500020)
will add hours greater than 20.

However, you should include the =20 case in one or the other, so use
--($D$10:$D$5000<20) and --($D$10:$D$5000=20)
or
--($D$10:$D$5000<=20) and --($D$10:$D$500020)

Sorry Stephen, Its hard to explain.
Here is a sample of my sheet ("Main"). Sheet2 is just a summary sheet.

A B D E
Date Client Hours Total hours
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
and so on.....
I am after a formula that would extract the following:

sheet2:
cell H9: Client "A" hours under 21:00 (from col E) = 15:25 hours
cell N9: Client "B" hours under 21:00 (from col E) = 5:35 hours

cell H11: Client "A" hours over 21:00 (from col E) = 0:15 hours
cell N11: Client "B" hours over 21:00 (from col E) = 7:55 hours

I hope this helps. If you wish more info please do not hesitate in asking
for it.

Thank you.

--
aac


"Stephen" wrote:

For what I thought you wanted, what I intended was two separate formulas,
one with a condition <20 and the other with a condition =20, like this:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),--($E$10:$E$5000<20),$D$10:$D$5000)

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),--($E$10:$E$5000=20),$D$10:$D$5000)

You had both conditions in the same formula - both cannot be true together!
Also, you had missed some commas.

But I'm afraid I'm baffled as to whether or not this is what you are trying
to calculate.

"aac" wrote in message
...
Hi Stephen,
Thanks for that. But its not quit there. At the moment it sum any single
value under 20 and over 20. I need it to sum under a running total of 20.
Give you more info, single entries could be anything from 5 minutes
upwards.
So its a cumulative total. I do have a col E10 to E5000 which keeps a
running
total.

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002)--($E$10:$E$5000<20)--($E$10:$E$5000=20),$D$10:$D$5000)
Have I added them in the right place?

--
aac


"Stephen" wrote:

"aac" wrote in message
...
Iv got the following code:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),$D$10:$D$5000)

P3 is date from
R3 is date to
col A is date
col B is name
col D is hours
That gives me a breakdown of hours for both clients.

I need to further brake the hours down to find out what hours the
clients
used under 20 hours and over as well.

--
aac

Add another condition to your SUMPRODUCT formula:
--($D$10:$D$5000<20)
will only add hours which are <20, whilst
--($D$10:$D$500020)
will add hours greater than 20.

However, you should include the =20 case in one or the other, so use
--($D$10:$D$5000<20) and --($D$10:$D$5000=20)
or
--($D$10:$D$5000<=20) and --($D$10:$D$500020)

I forgot to mention also in sheet 2:
P3 is date from
R3 is date to
--
aac


"aac" wrote:

Sorry Stephen, Its hard to explain.
Here is a sample of my sheet ("Main"). Sheet2 is just a summary sheet.

A B D E
Date Client Hours Total hours
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
and so on.....
I am after a formula that would extract the following:

sheet2:
cell H9: Client "A" hours under 21:00 (from col E) = 15:25 hours
cell N9: Client "B" hours under 21:00 (from col E) = 5:35 hours

cell H11: Client "A" hours over 21:00 (from col E) = 0:15 hours
cell N11: Client "B" hours over 21:00 (from col E) = 7:55 hours

I hope this helps. If you wish more info please do not hesitate in asking
for it.

Thank you.

--
aac


"Stephen" wrote:

For what I thought you wanted, what I intended was two separate formulas,
one with a condition <20 and the other with a condition =20, like this:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),--($E$10:$E$5000<20),$D$10:$D$5000)

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),--($E$10:$E$5000=20),$D$10:$D$5000)

You had both conditions in the same formula - both cannot be true together!
Also, you had missed some commas.

But I'm afraid I'm baffled as to whether or not this is what you are trying
to calculate.

"aac" wrote in message
...
Hi Stephen,
Thanks for that. But its not quit there. At the moment it sum any single
value under 20 and over 20. I need it to sum under a running total of 20.
Give you more info, single entries could be anything from 5 minutes
upwards.
So its a cumulative total. I do have a col E10 to E5000 which keeps a
running
total.

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002)--($E$10:$E$5000<20)--($E$10:$E$5000=20),$D$10:$D$5000)
Have I added them in the right place?

--
aac


"Stephen" wrote:

"aac" wrote in message
...
Iv got the following code:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),$D$10:$D$5000)

P3 is date from
R3 is date to
col A is date
col B is name
col D is hours
That gives me a breakdown of hours for both clients.

I need to further brake the hours down to find out what hours the
clients
used under 20 hours and over as well.

--
aac

Add another condition to your SUMPRODUCT formula:
--($D$10:$D$5000<20)
will only add hours which are <20, whilst
--($D$10:$D$500020)
will add hours greater than 20.

However, you should include the =20 case in one or the other, so use
--($D$10:$D$5000<20) and --($D$10:$D$5000=20)
or
--($D$10:$D$5000<=20) and --($D$10:$D$500020)

Thank you for your attention. I shall keep an eye on this post for any reply.
But in the meantime I shall repost this question.
--
aac


"aac" wrote:

I forgot to mention also in sheet 2:
P3 is date from
R3 is date to
--
aac


"aac" wrote:

Sorry Stephen, Its hard to explain.
Here is a sample of my sheet ("Main"). Sheet2 is just a summary sheet.

A B D E
Date Client Hours Total hours
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
and so on.....
I am after a formula that would extract the following:

sheet2:
cell H9: Client "A" hours under 21:00 (from col E) = 15:25 hours
cell N9: Client "B" hours under 21:00 (from col E) = 5:35 hours

cell H11: Client "A" hours over 21:00 (from col E) = 0:15 hours
cell N11: Client "B" hours over 21:00 (from col E) = 7:55 hours

I hope this helps. If you wish more info please do not hesitate in asking
for it.

Thank you.

--
aac


"Stephen" wrote:

For what I thought you wanted, what I intended was two separate formulas,
one with a condition <20 and the other with a condition =20, like this:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),--($E$10:$E$5000<20),$D$10:$D$5000)

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),--($E$10:$E$5000=20),$D$10:$D$5000)

You had both conditions in the same formula - both cannot be true together!
Also, you had missed some commas.

But I'm afraid I'm baffled as to whether or not this is what you are trying
to calculate.

"aac" wrote in message
...
Hi Stephen,
Thanks for that. But its not quit there. At the moment it sum any single
value under 20 and over 20. I need it to sum under a running total of 20.
Give you more info, single entries could be anything from 5 minutes
upwards.
So its a cumulative total. I do have a col E10 to E5000 which keeps a
running
total.

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002)--($E$10:$E$5000<20)--($E$10:$E$5000=20),$D$10:$D$5000)
Have I added them in the right place?

--
aac


"Stephen" wrote:

"aac" wrote in message
...
Iv got the following code:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!P$3),--($A$10:$A$5000<=Sheet2!R$3),--($B$10:$B$5000=MAIN!B5002),$D$10:$D$5000)

P3 is date from
R3 is date to
col A is date
col B is name
col D is hours
That gives me a breakdown of hours for both clients.

I need to further brake the hours down to find out what hours the
clients
used under 20 hours and over as well.

--
aac

Add another condition to your SUMPRODUCT formula:
--($D$10:$D$5000<20)
will only add hours which are <20, whilst
--($D$10:$D$500020)
will add hours greater than 20.

However, you should include the =20 case in one or the other, so use
--($D$10:$D$5000<20) and --($D$10:$D$5000=20)
or
--($D$10:$D$5000<=20) and --($D$10:$D$500020)