Hello Smart Ones!

I am looking to create a COUNTIF statement in Excel 2007 that will show me the number of times that a date appears in a given range. The only problem is the date I have is a long date, which all contain unique values. I want to use the INT function with the COUNTIF function, but am getting nowhere fast. I get the error "Your formula contains and error." (IMG:style_emoticons/default/iconfused.gif) Here is my formula: =COUNTIF(INT(D:D),INT(C2)).

The problem is column "D" is a long date, i.e. "2/1/2010 7:54:14 AM." They are all different values, so I want to strip out just the short date to compare. It works on the criterion section [INT(C2)], but not the range section ["INT(D:D)"].

Is there a way to do this without creating a new column for just the short date? Thanks in advance for all your help!

Jordan
(IMG:style_emoticons/default/uarulez2.gif)

This post has been edited by jordanjones: Mar 2 2010, 05:50 PM

Hi Jordan,

Try =SUMPRODUCT(--(INT(D:D)=INT(C2)))


HTH
Stu

Thanks for the quick response, Stu. I must not be using the formula, right. I am getting #VALUE when I plug the formula in. Any idea what I may be missing here?

Hi Jordan,

If you are using another version of Excel other than 2007, you cannot use the entire column in this function. Try:

=SUMPRODUCT(--(INT(D1:D65535)=INT(C2)))

HTH