sumproduct with criterias

[aac]

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.

 

[Stephen]

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.

Add another condition to your SUMPRODUCT formula:
--($D$10:$D$5000<20)
will only add hours which are <20, whilst
--($D$10:$D$5000>20)
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$5000>20)

 

[aac]

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?

 

[Stephen]

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]

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]

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

 

[aac]

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.