SUMPRODUCT with dates
 

How do I change the formula below so that it works with dates?
=SUMPRODUCT(--($E$2:$E$1043="CA*"),--($D$2:$D$1043=31/12/1999))
as it returns '0' at the moment whereas there should be 100's
Essentially, count the number of instances 'CA' appears in column E when the
date is on or after 31/12/99.
--
Traa Dy Liooar

Jock

SUMPRODUCT with dates
 

$D$2:$D$1043=--"31/12/1999"

--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Jock" wrote:

How do I change the formula below so that it works with dates?
=SUMPRODUCT(--($E$2:$E$1043="CA*"),--($D$2:$D$1043=31/12/1999))
as it returns '0' at the moment whereas there should be 100's
Essentially, count the number of instances 'CA' appears in column E when the
date is on or after 31/12/99.
--
Traa Dy Liooar

Jock

SUMPRODUCT with dates
 

=SUMPRODUCT(--($E$2:$E$1043="CA*"),--($D$2:$D$1043=date(1999,12,31))

Did you really mean to include Dec 31, 1999?

If no, you could just check the year:
=SUMPRODUCT(--($E$2:$E$1043="CA*"),--(year($D$2:$D$1043)2000))


Jock wrote:

How do I change the formula below so that it works with dates?
=SUMPRODUCT(--($E$2:$E$1043="CA*"),--($D$2:$D$1043=31/12/1999))
as it returns '0' at the moment whereas there should be 100's
Essentially, count the number of instances 'CA' appears in column E when the
date is on or after 31/12/99.
--
Traa Dy Liooar

Jock

--

Dave Peterson