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

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


"RDC" wrote:

> 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

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


--
Biff
Microsoft Excel MVP


"RDC" <(E-Mail Removed)> wrote in message
news:AB6BC7EE-0E50-4217-9A95-(E-Mail Removed)...
>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