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]

solved

=SUMPRODUCT(--(L2:L100=Q18),--(H2:H100=Q19),--(I2:I100<=Q16),--(K2:K100>=Q17),M2:M100)

reads the date range now , sorry for wasting anyones time

 

[Andrew Poulsom]

Isn't it?

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

 

[kieranbop]

ye I just got it above and realised where I was going wrong, thanks for the quick reply though

 

[kieranbop]

actually it doesnt seemed to have done it entirely.


Start End
<TABLE style="WIDTH: 440pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=585 border=0><COLGROUP><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7094" width=194><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7241" width=198><COL style="WIDTH: 145pt; mso-width-source: userset; mso-width-alt: 7058" width=193><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 146pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=194 height=18>01/01/2011</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 149pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=198></TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 145pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=193>12/12/2011 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>02/01/2011</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">13/12/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>03/01/2011</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">14/12/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>04/01/2011</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">15/12/2011</TD></TR></TBODY></TABLE>

date range I input:

1/1/2011 until 26/12/2011 will show me nothing back due to the end date finishing before, but the aim was tell me everything between said range so should tell me all its cores, whereas if I went
4/1/2011 - 15/12/2011 it would tell me them all.

I need to get it to tell me all things between a date range, so help is once again needed please.

 

[Andrew Poulsom]

In your list there are no entries that were active for the entire period 1/1/2011 to 26/12/2011. Is this what you want?

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

 

[kieranbop]

I want it to say if anything was active during those dates then display aswell as what the current formula does at the moment where if somethings active during two dates then display.

 

[Andrew Poulsom]

Did the formula I posted give you the results you expect?

 

[kieranbop]

yes and no

if I had two dates

1/1/2011
12/12/2011


and I input

3/3/2011
6/6/2011 it would output cores

but if I put

1/1/2011
13/12/2011 I would get nothing showing?

I'l leave it how it is at the moment where it just reads between the date range instead of outside it aswell. thanks for your help

 

[Andrew Poulsom]

Is it?

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