Count number of cells that fall within a range of dates

I am creating a log that keeps track of how many of a certain product are sold within the month. The product is always sold one at a time, so i just need a formula that can will look at the date column and report how many entries fall within the given month. The date is entered in the following format: mm/dd/yy. I have tried using the COUNTIF function:

=COUNTIF(log!A2:log!A3000, CRITERIA)

However, I do not know how to set up the CRITERIA to calculate all dates within a month, since the dates are not entered just as month and year, but include the day.

Hi,

Try something like this:

=SUMPRODUCT(--(TEXT(log!A2:A3000,"MMYY"="0805"))
or
=SUMPRODUCT(--(MONTH(log!A2:A3000)=8),--(YEAR(log!A2:A3000)=2005))
or
=SUMPRODUCT(--(log!A2:A3000>=--"2005-08-01"),--(log!A2:A3000<=--"2005-08-31"))


Regards,
KL


"mmay321" <[email protected]> wrote in
message news:[email protected]...
>
> I am creating a log that keeps track of how many of a certain product
> are sold within the month. The product is always sold one at a time, so
> i just need a formula that can will look at the date column and report
> how many entries fall within the given month. The date is entered in
> the following format: mm/dd/yy. I have tried using the COUNTIF
> function:
>
> =COUNTIF(log!A2:log!A3000, CRITERIA)
>
> However, I do not know how to set up the CRITERIA to calculate all
> dates within a month, since the dates are not entered just as month and
> year, but include the day.
>
>
> +-------------------------------------------------------------------+
> |Filename: Z-Gun SN Log.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=3677 |
> +-------------------------------------------------------------------+
>
> --
> mmay321
> ------------------------------------------------------------------------
> mmay321's Profile:
> http://www.excelforum.com/member.php...o&userid=25492
> View this thread: http://www.excelforum.com/showthread...hreadid=393989
>

this worked great:
=SUMPRODUCT(--(MONTH(log!A2:A3000)=8),--(YEAR(log!A2:A3000)=2005))

thanks,
michelle

Try counting all the cells "greater than" your minimum date and subtracting
a count of all the cells "greater than" your maximum date..........the
result will be a count of those cells within the two dates.........

Vaya con Dios,
Chuck, CABGx3


"mmay321" <[email protected]> wrote in
message news:[email protected]...
>
> I am creating a log that keeps track of how many of a certain product
> are sold within the month. The product is always sold one at a time, so
> i just need a formula that can will look at the date column and report
> how many entries fall within the given month. The date is entered in
> the following format: mm/dd/yy. I have tried using the COUNTIF
> function:
>
> =COUNTIF(log!A2:log!A3000, CRITERIA)
>
> However, I do not know how to set up the CRITERIA to calculate all
> dates within a month, since the dates are not entered just as month and
> year, but include the day.
>
>
> +-------------------------------------------------------------------+
> |Filename: Z-Gun SN Log.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=3677 |
> +-------------------------------------------------------------------+
>
> --
> mmay321
> ------------------------------------------------------------------------
> mmay321's Profile:
http://www.excelforum.com/member.php...o&userid=25492
> View this thread: http://www.excelforum.com/showthread...hreadid=393989
>