+ Reply to Thread
Results 1 to 9 of 9

Count on Date Range

  1. 06-21-2012, 01:54 AM #1
    ssdsibes
    ssdsibes is offline
    Registered User
    Join Date
    06-09-2009
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2003
    Posts
    91

    Count on Date Range

    I have defined a name on a list in have in my worksheet

    Please Login or Register  to view this content.

    I now want to count how many entries there are in that list, that have a date less that todays date - 100 days.

    i tried this

    Please Login or Register  to view this content.
    but it doesn't return any values. There are 5 dates that met the criteria.
    i have attached my spreadsheet, and hightlighted the 5 dates that met the criteria

    Any help greatly appreciated
    Attached Files Attached Files
    Last edited by ssdsibes; 06-21-2012 at 02:42 AM.
    Reply With QuoteRegister To Reply
  2. 06-21-2012, 02:17 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: Count on Date Range

    Maybe COUNTIF instead of just COUNT.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 06-21-2012, 02:22 AM #3
    tom1977
    tom1977 is offline
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Count on Date Range

    Hi try this 
    Please Login or Register  to view this content.
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.
    Reply With QuoteRegister To Reply
  4. 06-21-2012, 02:23 AM #4
    ssdsibes
    ssdsibes is offline
    Registered User
    Join Date
    06-09-2009
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Count on Date Range

    I have just tried COUNTIF and even put the date formula in another cell that i referenced. the cell A1 has the following formula

    Please Login or Register  to view this content.
    then i used the following formula
    Please Login or Register  to view this content.
    but this still returned a 0 Value
    Reply With QuoteRegister To Reply
  5. 06-21-2012, 02:24 AM #5
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count on Date Range

    Hi

    Corect result is 6. Not 5

    =COUNTIF(JNM_Over100,"<"&TODAY()-100)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  6. 06-21-2012, 02:30 AM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count on Date Range

    =COUNTIF(JNM_Over100,"<"&A1)
    Reply With QuoteRegister To Reply
  7. 06-21-2012, 02:33 AM #7
    san2525
    san2525 is offline
    Registered User
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    34

    Re: Count on Date Range

    Date Value.xls

    Solved Bro

    Key formula : =SUMIF(D4:D23,">"&D28&"",I4:I23)

    where D28 is the date with which u wanna check .. u can use today () also ryt in D28

    I4:I23 is a new colum dat contains 1 only for counting



    cheers
    SANDEEP
    << press the star * if u like
    Reply With QuoteRegister To Reply
  8. 06-21-2012, 02:41 AM #8
    ssdsibes
    ssdsibes is offline
    Registered User
    Join Date
    06-09-2009
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Count on Date Range

    Excellent, thank you that worked, and yes, my counting ability is bad, hence why i need excel.
    Reply With QuoteRegister To Reply
  9. 06-21-2012, 02:55 AM #9
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count on Date Range

    .....and yes, my counting ability is bad, hence why i need excel.
    Ha Ha....!

    You are welcome.

    Excel, never says lies!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1