Show if between two dates
 

Hi everyone

I am trying to count that if the name in T21 is anywhere in column D and the
corresponding cell in column A is between the 01/01/2010 and the 31/01/2010

I have written the following but its not working - where am I falling down
please?

=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=01/01/2010,<31/01/2010))

Thanks as always

Lise

Show if between two dates
 

=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=01/01/2010,<31/01/2010))

Use cells to hold the date boundaries.

F1 = lower date boundary
G1 = upper date boundary

=SUMPRODUCT(--($D$2:$D$618=T21),--($A$2:$A$618=F1),--($A$2:$A$618<G1))

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
Hi everyone

I am trying to count that if the name in T21 is anywhere in column D and
the
corresponding cell in column A is between the 01/01/2010 and the
31/01/2010

I have written the following but its not working - where am I falling down
please?

=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=01/01/2010,<31/01/2010))

Thanks as always

Lise

Show if between two dates
 

To Excel, 01/01/2010 in a formula means 1 divided by 1 divided by 2010. If
you want a date, you need to say so, as in:
=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=DATE(2 010,1,1))*($A$2:$A$618<DATE(2010,1,31)))

Regards,
Fred

"Lise" wrote in message
...
Hi everyone

I am trying to count that if the name in T21 is anywhere in column D and
the
corresponding cell in column A is between the 01/01/2010 and the
31/01/2010

I have written the following but its not working - where am I falling down
please?

=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=01/01/2010,<31/01/2010))

Thanks as always

Lise

Show if between two dates
 

Fabulous - thanks to you both :-)

--
Thanks as always

Lise


"Fred Smith" wrote:

To Excel, 01/01/2010 in a formula means 1 divided by 1 divided by 2010. If
you want a date, you need to say so, as in:
=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=DATE(2 010,1,1))*($A$2:$A$618<DATE(2010,1,31)))

Regards,
Fred

"Lise" wrote in message
...
Hi everyone

I am trying to count that if the name in T21 is anywhere in column D and
the
corresponding cell in column A is between the 01/01/2010 and the
31/01/2010

I have written the following but its not working - where am I falling down
please?

=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=01/01/2010,<31/01/2010))

Thanks as always

Lise

.

Show if between two dates
 

You're welcome. Thanks for the feedback.

Fred.

"Lise" wrote in message
...
Fabulous - thanks to you both :-)

--
Thanks as always

Lise


"Fred Smith" wrote:

To Excel, 01/01/2010 in a formula means 1 divided by 1 divided by 2010.
If
you want a date, you need to say so, as in:
=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=DATE(2 010,1,1))*($A$2:$A$618<DATE(2010,1,31)))

Regards,
Fred

"Lise" wrote in message
...
Hi everyone

I am trying to count that if the name in T21 is anywhere in column D
and
the
corresponding cell in column A is between the 01/01/2010 and the
31/01/2010

I have written the following but its not working - where am I falling
down
please?

=SUMPRODUCT(($D$2:$D$618=T21)*($A$2:$A$618=01/01/2010,<31/01/2010))

Thanks as always

Lise

.