+ Reply to Thread
Results 1 to 12 of 12

count if greater than date in cell E1.

  1. #1
    Forum Contributor
    Join Date
    10-18-2012
    Location
    new york
    MS-Off Ver
    Excel 2013
    Posts
    152

    count if greater than date in cell E1.

    hi
    i would like 3 formulas for countif
    i would like to count all cells that contain a date in column A that is more than 30 days past the date in cell E1. i would also like to count all cells that contain a date in column A that is more than 3 days past the date in cell E1 (includes those over 30 days). i would then like to count all cells that contain a date 0-3 days more than the date in cell E1.
    thank you

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,438

    Re: count if greater than date in cell E1.

    Try 3 below formula:
    =COUNTIF(A:A,">"&(E1+30))
    =COUNTIF(A:A,">"&(E1+3))
    =COUNTIFS(A1:A4,">="&E1,A1:A4,"<="&(E1+3))

  3. #3
    Forum Contributor
    Join Date
    10-18-2012
    Location
    new york
    MS-Off Ver
    Excel 2013
    Posts
    152

    Re: count if greater than date in cell E1.

    hi, thank you, for some reason it is bringing back a 0 value though??

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,438

    Re: count if greater than date in cell E1.

    Is there any chance to post a small example?

  5. #5
    Forum Contributor
    Join Date
    02-01-2011
    Location
    london, england
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: count if greater than date in cell E1.

    apologies, i'll open new thread.
    Last edited by fabrecass; 01-22-2013 at 12:01 PM.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,438

    Re: count if greater than date in cell E1.

    @fabrecass,
    please open a new thread for your own query

  7. #7
    Forum Contributor
    Join Date
    10-18-2012
    Location
    new york
    MS-Off Ver
    Excel 2013
    Posts
    152

    Re: count if greater than date in cell E1.

    here is the example
    Attached Files Attached Files

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: count if greater than date in cell E1.

    Quote Originally Posted by bebo021999 View Post
    Try 3 below formula:
    =COUNTIF(A:A,">"&(E1+30))
    =COUNTIF(A:A,">"&(E1+3))
    =COUNTIFS(A1:A4,">="&E1,A1:A4,"<="&(E1+3))
    Quote Originally Posted by ea223 View Post
    here is the example
    The formulas that bebo021999 posted will do what you want.

    In your sample file there are no dates that meet the first two conditions.

    The formula for the third condition just needs to have the range address expanded:

    =COUNTIFS(A:A,">="&E1,A:A,"<="&E1+3)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Forum Contributor
    Join Date
    10-18-2012
    Location
    new york
    MS-Off Ver
    Excel 2013
    Posts
    152

    Re: count if greater than date in cell E1.

    Hi, ok I see what I have got confused here. I meant to say OLDER than the date in cell e1 not past!! so it would be more than 30 days prior to the date in E1. Very sorry for this confusion, I will try the formula with a - instead of a +?
    thanks

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: count if greater than date in cell E1.

    Ok, for dates OLDER than, try these:

    =COUNTIF(A:A,"<"&E1-30)

    =COUNTIF(A:A,"<"&E1-3)

    =COUNTIFS(A:A,">="&E1-3,A:A,"<="&E1)

  11. #11
    Forum Contributor
    Join Date
    10-18-2012
    Location
    new york
    MS-Off Ver
    Excel 2013
    Posts
    152

    Re: count if greater than date in cell E1.

    brilliant, thank you

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: count if greater than date in cell E1.

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1