Between 2 Calendar dates
 

I have 2 worksheets called 1) Data 2) between_2_dates.

The sheet called Data holds all the information.

I have the following formula in cell B2 of sheet between_2_dates:

=SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<=DATE(2008,01,01)))

I am using this calendar control to enter a date in cell D2 and I2 of sheet
between_2_dates:

http://www.rondebruin.nl/calendar.htm

This works fine, but I need to include these 2 dates in the formula above,
so DATE(2003,01,01) will be replaced and use the date in cell D2 and
DATE(2008,01,01) will be replaced and use the date in cell I2.

What would be the complete formula to do this?

Many thanks for any help with this.

--
Richard

Between 2 Calendar dates
 

If D2 and I2 are both dates, then you can substitute them directly,
i.e.:

=SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<*=I2))

Hope this helps.

Pete

On Jun 21, 12:44 pm, Richard
wrote:
I have 2 worksheets called 1) Data 2) between_2_dates.

The sheet called Data holds all the information.

I have the following formula in cell B2 of sheet between_2_dates:

=SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<*=DATE(2008,01,01)))

I am using this calendar control to enter a date in cell D2 and I2 of sheet
between_2_dates:

http://www.rondebruin.nl/calendar.htm

This works fine, but I need to include these 2 dates in the formula above,
so DATE(2003,01,01) will be replaced and use the date in cell D2 and
DATE(2008,01,01) will be replaced and use the date in cell I2.

What would be the complete formula to do this?

Many thanks for any help with this.

--
Richard

Between 2 Calendar dates
 

Do exactly what you say in your message: replace the dates with cells. Your new
formula would be:

=SUMPRODUCT(--(Data!$A$1:$A$10000=d2),--(Data!$A$1:$A$10000<=i2))

--
Regards,
Fred


"Richard" wrote in message
...
I have 2 worksheets called 1) Data 2) between_2_dates.

The sheet called Data holds all the information.

I have the following formula in cell B2 of sheet between_2_dates:

=SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<=DATE(2008,01,01)))

I am using this calendar control to enter a date in cell D2 and I2 of sheet
between_2_dates:

http://www.rondebruin.nl/calendar.htm

This works fine, but I need to include these 2 dates in the formula above,
so DATE(2003,01,01) will be replaced and use the date in cell D2 and
DATE(2008,01,01) will be replaced and use the date in cell I2.

What would be the complete formula to do this?

Many thanks for any help with this.

--
Richard

Between 2 Calendar dates
 

=SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<=I2))



Richard wrote:

I have 2 worksheets called 1) Data 2) between_2_dates.

The sheet called Data holds all the information.

I have the following formula in cell B2 of sheet between_2_dates:

=SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<=DATE(2008,01,01)))

I am using this calendar control to enter a date in cell D2 and I2 of sheet
between_2_dates:

http://www.rondebruin.nl/calendar.htm

This works fine, but I need to include these 2 dates in the formula above,
so DATE(2003,01,01) will be replaced and use the date in cell D2 and
DATE(2008,01,01) will be replaced and use the date in cell I2.

What would be the complete formula to do this?

Many thanks for any help with this.

--
Richard

--

Dave Peterson

Between 2 Calendar dates
 

Thank you everyone, that works fine.

I was trying to this <=DATE(D2) and wondered why it wasn't working.

--
Richard


"Dave Peterson" wrote:

=SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<=I2))



Richard wrote:

I have 2 worksheets called 1) Data 2) between_2_dates.

The sheet called Data holds all the information.

I have the following formula in cell B2 of sheet between_2_dates:

=SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<=DATE(2008,01,01)))

I am using this calendar control to enter a date in cell D2 and I2 of sheet
between_2_dates:

http://www.rondebruin.nl/calendar.htm

This works fine, but I need to include these 2 dates in the formula above,
so DATE(2003,01,01) will be replaced and use the date in cell D2 and
DATE(2008,01,01) will be replaced and use the date in cell I2.

What would be the complete formula to do this?

Many thanks for any help with this.

--
Richard

--

Dave Peterson