sumproduct with criterias
"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)
|