Counting dates greater than a certain time period

T

theseandavis

hey guys,

This seems relatively simple but I just can't nail it down!

I have 5000+ entries of dates in format 12/17/2001 etc. I need to
specifically count all those above the date 1/1/2002. Is there any way
to do this with a formula?
 
C

Chip Pearson

Use the COUNTIF function. E.g.,

=COUNTIF(A1:A10,"<"&DATE(2002,1,1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
T

theseandavis

Sorry for the hasty reply. I appear to be having a bit of difficulty. I
need to count these dats but only for entries that contain the word
"Active" in column A.

I'm trying to add the AND operator into COUNTIF but am not having any
success:(
 
W

William Horton

=SUMPRODUCT(--($A$2:$A$5000="Active"),--($B$2:$B$5000>DATE(2002,1,1)))

Where the Active / Inactive is in column A and the dates are in column B.

Hope this helps

Bill Horton
 
S

S Davis

Thank you both for your reply. It appears to work, but only partially.
Here is a sample of my data

......A..................B
1.......STATUS TERM DATE
2.......Active 1/24/2000
~.......
5403..Terminated 3/14/2005

Formulas I've used are as follows (along with their results) --->

=COUNTIF(A1:A5403,"Active") = 747
=SUMPRODUCT(--(A1:A5403="Active"),--(B1:B5403>DATE(2002,1,1))) =747
or
Thank you both for your reply. It appears to work, but only partially.
Here is a sample of my data

......A..................B
1.......STATUS TERM DATE
2.......Active 1/24/2000
~~~...
5403..Terminated 3/14/2005

Formulas I've used are as follows (along with their results) --->

=COUNTIF(A1:A5403,"Active") = 747
or
=SUMPRODUCT(--(A1:A5403="Active"),--(B1:B5403>DATE(2002,1,1))) =747
or
=SUMPRODUCT(--(A1:A5403="Active"),--(B1:B5403<DATE(9999,1,1))) =747
or
=SUMPRODUCT(--(A1:A5403="Active"),--(B1:B5403<DATE(2002,1,1))) =0

As you can see, SUMPRODUCT in this instance is not even recognizing
that I am inputting a date. Reversing the "<" gives me a result of 0,
and changing the year has no effect.

Any ideas? I really need this. Otherwise I fear this entire thing is
not going to work.
 
S

S Davis

Forgive me for making this the most convuluted post in the world, but I
have to correct a typo:

=SUMPRODUCT(--(A1:A5403="Active"),--(B1:B5403<DATE(9999,1,1))) =747


should read:

=SUMPRODUCT(--(A1:A5403="Active"),--(B1:B5403>DATE(9999,1,1))) =747


Sorry for the confusion:)
 
Top