# Sum Product issue

#### kieranbop

##### New Member
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:
Code:
``=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.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
solved

Code:
``=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

Isn't it?

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

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

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.

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)

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.

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

yes and no

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

Is it?

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

Replies
8
Views
196
Replies
5
Views
277
Replies
3
Views
458
Replies
5
Views
304
Replies
7
Views
488

1,206,755
Messages
6,074,757
Members
446,084
Latest member
WalmitAal

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back