Show if between two dates

[Lise]

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

 

[T. Valko]

=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))

 

[Fred Smith]

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(2010,1,1))*($A$2:$A$618<DATE(2010,1,31)))

Regards,
Fred

 

[Lise]

Fabulous - thanks to you both

 

[Fred Smith]

You're welcome. Thanks for the feedback.

Fred.

Fabulous - thanks to you both