Countif to count items within a range of dates !!PLEASE HELP!!
Posted by Ryan on May 24, 2000 9:46 AM
I have a large list of outstanding checks(31k rows) including 3 columns listing the check#,check date and the batch that the checks were done in. I need to count the number of checks within a given date range. I can use the countif to get the results of one day as follows:
=COUNTIF(C1:C31572, "04/28/2000")
That will work ok for one date, but how would I get the results of lets say, all of April 2000? Any help is greatly appreciated.
Thanks!
Ryan
PS. Please copy in response to my email addy(Ryan.Kamp1@firstunion.com
Posted by thomas venn on May 24, 2000 10:26 AM
hi Ryan,
here is a quick and dirty way of doing it. in column D, type in =MONTH(b1)&"/"&YEAR(b1), then copy and paste the formula down all the way to cell d31,000 or wherever your data ends. then you can count using your criteria on cell D, instead. here is an example of the Count formula : =COUNTIF($D$1:$D$35000,D1)
cheers,
thomas
Posted by Jaime on May 24, 2000 10:49 AM
=SUM(IF( ( MONTH(YOUR_RANGE)=4 )*(YEAR(YOUR_RANGE)=2000),1,0))
REMEMBER TO PRESS CTRL-SHFT-ENTER
Posted by JAF on May 25, 2000 12:21 AM
The answers given work fine if you want to count the entries for a single month, but if you want to specify 2 dates either within the same month or spanning more than one month you can use the following:
=SUM((A1:B23>=DATEVALUE("startdate"))*(A1:B23<=DATEVALUE("enddate")))
JAF
