PDA

View Full Version : Help with countif, 2 crteria required



rama4672
10-17-2008, 01:59 PM
Hi All
I Have a spreadsheet that I need help with a formula, in Cell N1 I have the following formula =COUNTIF(D6:D154,"<17:59")*(AND(C6:C180,11.25)) What I need it to do is count the amount of times 11.25 appear before a start time of 18:00, I have tried different ways to do this, but non that work.
I have attached a file!
Any help with this would be apprieciated

Thank You
Ian

Bob Phillips
10-17-2008, 02:15 PM
N1: =SUMPRODUCT(--($D$6:$D$154<--"18:00:00"),--($C$6:$C$154=11.25))

N2: =SUMPRODUCT(--($D$6:$D$154<--"18:00:00"),--($C$6:$C$154=9))

fb7894
10-17-2008, 05:04 PM
XLD, what is the "--" in your formulas?

Is there any advantage to your formulas verses
=SUMPRODUCT(($D$6:$D$154<--"18:00:00")*($C$6:$C$154=9)) ?

Bob Phillips
10-17-2008, 05:28 PM
There is, read about it at http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.

rama4672
10-18-2008, 05:48 AM
N1: =SUMPRODUCT(--($D$6:$D$154<--"18:00:00"),--($C$6:$C$154=11.25))

N2: =SUMPRODUCT(--($D$6:$D$154<--"18:00:00"),--($C$6:$C$154=9))

Thank you XLD
It works to a certain extent, I have attached another file, I want the formula to look at thursday and calculate all the 11.25 after and including 18:00, and then look at Friday to calculate all the 11.25 before but not including 18:00 in cell N1, what I am finding it is doing is also counting the 11.25 where it has got some one who is on the sick or holiday, I only want the calculation if they are on shift and not sick or on holiday
Thanks
Ian

rama4672
10-20-2008, 03:15 PM
I have managed to figure it out to give me the desired result, the formula look huge though, Would this be the best way to tackle this.


N1: =SUMPRODUCT(--(Thursday!$D$6:$D$154>--"17:59:00"),--(Thursday!$C$6:$C$154=11.25))-SUMPRODUCT(--(Thursday!$D$6:$D$154="hol"),--(Thursday!$C$6:$C$154=11.25))-SUMPRODUCT(--(Thursday!$D$6:$D$154="sick"),--(Thursday!$C$6:$C$154=11.25))+SUMPRODUCT(--($D$6:$D$154<--"17:59:00"),--($C$6:$C$154=11.25))

If this is the only way to do it, I will then mark this as Solved.

Thanks
Ian