Sum Product issue

[kieranbop]

I've been having a slight issue with a formula I'm trying to implement.
I have two dates:

Start Datej27) 1/1/2011 -------------------- i2:i100
End Date: (j28) 12/12/2011-------------------- K2:K100

I have two criteria:
Silo: Silo 1(J29) -------------------- L2:L100
Priority: 1 (J30)-------------------- H2:H100
If all criteria is met then it calculates cores in column M.

Formula I had in place is or was:

=SUMPRODUCT(--(L2:L100=J29),--(H2:H100=J30),--(I2:I100>=J27),--(K2:K100>=J28),M2:M100)

Works fine in the sense that the two dates start and end if it matches that of the worksheet then its fine. But Instead of me inputting the two dates above, I want it to let me input dates inbetween say
1/3/2011 until 1/5/2011 and it recognises that i have a project running between those dates although it doesnt start and finish then.


So instead of saying if your start and end date is between these two dates say that if your date range falls anywhere within the two date ranges I input then display me the cores.


Thanks for any help or input anyone may have.

 

[kieranbop]

you cant use that it changes it to:

=SUMPRODUCT(--(L2:L100=S19),--(H2:H100=S20),--(I2:I100<S17),--(K2:K100>S18),M2:M100)

and that doesnt work i'm afraid

regards
K

 

[kieranbop]

take the bars as the date ranges


1/1/2011 ========================== 20/1/2011
3/1/2011===========================18/1/2011

I input date range 2/1/2011 until 19/1/2011 I only get the tops data when the second one is active at some point during that date range, I need it to get it to calculate that aswell.

Is that any clearer?