SUMPRODUCT with dates

  • Thread starter Thread starter Jock
  • Start date Start date
J

Jock

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.
 
=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))
 
Back
Top