Countif and dates

[ChrisHJH]

Hi,

I have several coumns of data, all of them are dates. I want to add some statistical information in the worksheet telling me which dates are in which range in several of the columns.

I thought countif would be ideal for this so set about using it ... and this is where I need help

=countif(a:a, ">01/01/2011") - this works fine!

=countif(b:b, and(">01/01/2011","<01/01/2012")) - this returned 5 but I could count at least 50 rows which were between the two dates

I have made sure all the rows are formatted as date

Help please!

Thanks

Chris

 

[VoG]

With a date in B1 what does

=ISNUMBER(B1)

return?

 

[T. Valko]

Hi,

I have several coumns of data, all of them are dates. I want to add some statistical information in the worksheet telling me which dates are in which range in several of the columns.

I thought countif would be ideal for this so set about using it ... and this is where I need help

=countif(a:a, ">01/01/2011") - this works fine!

=countif(b:b, and(">01/01/2011","<01/01/2012")) - this returned 5 but I could count at least 50 rows which were between the two dates

I have made sure all the rows are formatted as date

Help please!

Thanks

Chris

Just an observation...

If you're wanting to count all dates within the year 2011 then your formula is not counting any dates of 1/1/2011.

You would want:

=COUNTIF(range,">="&DATE(2011,1,1))-COUNTIF(range,">"&DATE(2011,12,31))

Or:

=SUMPRODUCT(--(YEAR(range)=2011))