COUNTIF query

[reaper]

er..I have an excel datasheet where one of the fields has a date value...I'm trying to create a summary stats sheet to go with it.

What I want to do, in my head, is 'countif ("a value is between THIS date and THAT date")'

Countif probably isn't the best function, but I'm not an excel expert. Any ideas if/how this is possible?

[dominicb]

Good evening reaper

What I want to do, in my head, is 'countif ("a value is between THIS date and THAT date")'

Let's say that you have dates from A1:A20, and you want to count all dates between 01/07/08 and 10/07/08 (these dates are in English format - adjust to suit) then this formula should do the trick :

=SUMPRODUCT(--(A1:A20>=DATEVALUE("01/07/08")),--(A1:A20<=DATEVALUE("10/07/08")))

HTH

DominicB

[Cheeky Charlie]

These are lovely formulae, these sumproduct implicit arrays.

A couple of side notes:
As this particular example is a pair of arrays, only single negation is necessary to convert the true/false arrays to numbers (0s and -1s as opposed to 0s and 1s).
Also, date(year,month,day) has none of the ambiguity of datevalue("string"); an issue DominicB acknowledges

Following these notes, my ammendment would be:
=SUMPRODUCT(-(A1:A20>=DATE(2008,7,1)),-(A1:A20<=DATE(2008,7,10)))

HTH

[daddylonglegs]

or just put your two dates in cells, e.g. D2 and D3 and use either

=SUMPRODUCT(--(range>=D2),--(range<=D3))

or

=COUNTIF(range,">="&D2)-COUNTIF(range,">"&D3)