Counting dates greater than a certain time period

[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?

 

[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

 

[theseandavis]

thanks a lot

 

[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

 

[Dave Peterson]

=sumproduct(--(a1:a10="Active"),--(b1:b10<DATE(2002,1,1)))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Guest]

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