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?

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


wrote in message
oups.com...
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?

thanks a lot:)

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:(

=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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

wrote:

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(--($A$2:$A$5000="Active"),--($B$2:$B$5000DATE(2002,1,1)))

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

Hope this helps

Bill Horton

" wrote:

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:(

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:B5403DATE(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:B5403DATE(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.

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:B5403DATE(9999,1,1))) =747


Sorry for the confusion:)