Results 1 to 14 of 14

Thread: CountIF

  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CountIF

    I am using excel 97 and I want to count the number of entries between a date range. I have entered =countif(a1:a40,">09/01/01 and <11/30/01"). I only get zeros. Can anyone help??

    Thanks,
    Deborah

  2. #2
    Eve B
    Guest

    Re: CountIF

    I don't think you can do more than one parameter in countif. I tried this out and it seems to work:

    =COUNTA(A1:A40)-(COUNTIF(A1:A40,">11/29/01")+COUNTIF(A1:A40,"<9/2/01"))

    This says to:
    count all the non-blank cells,
    then subtract the sum of
    # of cells greater with values greater than Nov 29 and
    # of cells less with values less than Sept 1

    assuming that you want to count any dates from 9/2 to 11/29 only. If you really want to count Sept, Oct & Nov days only, then it should read >11/30 and <9/1.

    I hope this helps!

    Eve

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIF

    If you'd like to see a great discussion of date intervals, go to <A target="_blank" HREF=http://www.cpearson.com/excel/DateIntervals.htm>Chip Pearson's web site</A>.
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: CountIF

    Hi
    Does this work ?

    =countif(Range,>=MinDate)-countif(range,>MaxDate)

    Cheers
    Geof

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: CountIF

    Hi Deborah
    Try this userdefined function

    Function CountDateBetween(inRange, minDate As Date, maxDate As Date)
    Dim MinCtr, MaxCtr As Integer 'counter for dates exceeding limits
    MinCtr = Application.CountIf(inRange, ">=" & minDate)
    MaxCtr = Application.CountIf(inRange, ">" & maxDate)
    CountDateBetween = MinCtr - MaxCtr
    End Function

    Copy & paste into the personal workbook macros. This workbook is normally hidden.
    You will be then able to invoke thefunction using the function wizard (fx button) from the user defined category.

    Cheers
    Geof

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIF

    Whenever I received your post through my email it had < before the last date range. I can follow the logic up until I hit this. I know the & is a concatenation character but what are the others? The formula works great!!

    Thanks,
    Deborah

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIF

    The following array formula should give you what you are asking for:

    <pre>=SUM((A1:A40 > DATEVALUE("9/1/01")) * (A1:A40 < DATEVALUE("11/30/01")))
    </pre>


    That is an array formula, so you must hold down the Ctrl and Shift keys when you press the Enter key to enter the formula into the cell.
    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIF

    Here is the formula again before I try to explain:

    <pre>=SUM((A1:A40 > DATEVALUE("9/1/01")) * (A1:A40 < DATEVALUE("11/30/01")))
    </pre>


    Since this is entered as an array formula, the (A1:A40 > DATEVALUE("9/1/01") part of the formula will compare the dates in cells A1 through A40 to the date 9/1/01 and create an array of forty ones or zeros. There will be a one in the array where the date in the corresponding cell is greater than 9/1/01, and a zero where it is less. The second part of the formula creates a second array with ones where the date is less than 11/30/01 and a zero where it is greater. The asterich (*) multiplies the those two arrays together to create an array where the elements are one if the date in the corresponding cell is between the dates and a zero if it is outside the range. The SUM adds uo the elements in this array which counts the dates in the range.

    There is no & in the formula, so I don't know what you are asking there.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: CountIF

    Legare, when Lounge messages are e-mailed, the less than "<" symbol gets converted to "& l t" and the greater than ">" symbol gets converted to "& g t ", without the spaces.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIF

    John: Thanks. Does that happen if the formula is inside pre tags? I had tried to prevent that by using pre tags, but had a typo which converted [ pre ] to [pre}. I don't use the email options, so I don't know what happens there.
    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIF

    Thanks for the clarification!!!! The formula works great and I appreciate all the post!

    Thanks,
    Deborah

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: CountIF

    I'm not certain, send a test to me with preserve tags and I'll check.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: CountIF

    Gee, John, you could send a test to YOURSELF and check. Doh!

    greater than<pre>></pre>

    less than<pre><</pre>

    asterisk<pre>*</pre>

    equals<pre>=</pre>

    number/pound/hash/sharp<pre>#</pre>

    ampersand<pre>&</pre>

    caret<pre>^</pre>

    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: CountIF

    Here's what e-mail delivers (item in quotes edited to add spaces):
    greater thanless than"& l t";asterisk*equals=number/pound/hash/sharp#ampersand&caret^

    The greater than symbol disappears altogether, as does the "pre" spacing.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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