Microsoft Excel - Count range of cells within a date range on a second spreadsheet

Asked By Hannah Smith on 18-Sep-08 05:22 PM

I have a spreadsheet that I call my "Data Sheet" setup that I would like to populate with information from a second spreadsheet, "Open Data" .  I need to count a range of cells containing data equal to "Dogs" in column G between a date range of 08/01/2008 and 08/31/2008 in column E.  I also need to count the data equal to "Cats" and "Horses" in the same manner and populate different cells on the "Data Sheet".  I am have tried entering the following formula on the "Data Sheet" without success:

 =COUNTIF(('Open Data'!G:G,="Dogs")*('Open Data'!E:E>="8/1/2008")*('Open Data'!E:E<="8/31/2008"))

Could someone help me with this formula?



Try this - ram kumar replied to Hannah Smith on 18-Sep-08 10:23 PM

Hi,

This is sample only

I am a bit confused as to what you are after. If you simply want to count the total number of dates in a list that fall between two dates (inclusive) then you could use (for a list of dates in cells B1:B60)

--------------------------------------------------------------------------------
Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas   Z
100  =SUMPRODUCT(($B$1:$B$60>=$D$1)*($B$1:$B$60<=$D$2))  

--------------------------------------------------------------------------------

Where cells D1 and D2 contain your starting and ending dates.

If you want to find the occurrences of specific dates, then use

--------------------------------------------------------------------------------
Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas   Z
100  =SUMPRODUCT(($B$1:$B$60=F1)*1)  

Try this - ram kumar replied to Hannah Smith on 18-Sep-08 10:24 PM

Hi,

The first Countif counts the number of dates in the given range greater than the 'start date'. The second Countif subtracts any date greater than the 'end date'.

=COUNTIF(A1:A9,">1/1/2007")-COUNTIF(A1:A9,">8/1/2007")

"I need is to search for the number of times any date in a given period occurs i.e. number of 03/07/07s in a period"

where "period" becomes a range (A1:A9) is:

=COUNTIF(A1:A9,">2/1/2007")-COUNTIF(A1:A9,">3/1/2007")

To use the UDF below, enter

=DCnt(

in a cell. Then select the range (or type it in) then a comma ie:

=DCnt(A1:A9,

Now either select a cell that has the start date or enter a date in quotes and a comma ie:

either =DCnt(A1:A9,E2,

or =DCnt(A1:A9,"1/1/2007",

Then finish up with another range or quoted date and a final parentheses:

either =DCnt(A1:A9,E2,E3)

or =DCnt(A1:A9,"1/1/2007","1/7/2007")

Press enter.


VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
 
Option Explicit
 
Function DCnt(rng As Range, sdate As Date, edate As Date)
    
    Dim Cel As Range
    Dim Countr As Long
    
    For Each Cel In rng
        If Cel > sdate And Cel < edate Then
            Countr = Countr + 1
        End If
    Next Cel
    
    DCnt = Countr
    
End Function
 


COUNTIFS - mv ark replied to Hannah Smith on 19-Sep-08 10:15 AM

Try something like this -
=COUNTIFS(Sheet1!$A$2:$A$5,"=dog",Sheet1!$B$2:$B$5,"<"&DATE(2008,4,2))