date range

[RDC]

I want to calculate the number of times a ? is in one column (T) when the
date is less than 28/02/09 in column G. I have tried numerous formula, yet it
either counts all the ?,s without taking into account the date or gives me a
figure of 0.

The examples of what I have used is

=SUMPRODUCT(--('LTS Pipeline'!T$12:T$2000="?"),--('LTS
Pipeline'!G$12:G$2000<="28-Feb-09"))

Can anyone help!?!?

Many thanks

 

[Joel]

You need to convert the date into a serial date

from
=SUMPRODUCT(--('LTS Pipeline'!T$12:T$2000="?"),--('LTS
Pipeline'!G$12:G$2000<="28-Feb-09"))

to
=SUMPRODUCT(--('LTS Pipeline'!T$12:T$2000="?"),--('LTS
Pipeline'!G$12:G$2000<=DateValue("28-Feb-09")))

 

[T. Valko]

Try one of these:

=SUMPRODUCT(--('LTS Pipeline'!T$12:T$2000="?"),--('LTS
Pipeline'!G$12:G$2000<=DATE(2009,2,28)))

Better to use a cells to hold the criteria:

A1 = ?
B1 = 28/2/2009

=SUMPRODUCT(--('LTS Pipeline'!T$12:T$2000=A1),--('LTS
Pipeline'!G$12:G$2000<=B1))

Note that if cells in 'LTS Pipeline'!G$12:G$2000 are empty they will
evaluate to be less than 28/2/2009. If you need to account for that:

=SUMPRODUCT(--('LTS Pipeline'!T$12:T$2000=A1),--('LTS
Pipeline'!G$12:G$2000<>""),--('LTS Pipeline'!G$12:G$2000<=B1))