countif using dates
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: countif using dates

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a spreadsheet that has dates in one column formatted as dd/mm/yyyy and I'm trying to count the number of jan, feb, etc. occurances in this spreadsheet. I've used the countif function as so: =COUNTIF(F2:F74;=>"2/1/2002")+COUNTIF(F2:F74;=<"2/28/2002") and get an error result for the formula. I've also tried the sumif function and can't seem to get the right configuration for the formula. Any ideas/concerns/further suggestions would be GREATLY appreciated?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-19 11:34, rferley wrote:
    I have a spreadsheet that has dates in one column formatted as dd/mm/yyyy and I'm trying to count the number of jan, feb, etc. occurances in this spreadsheet. I've used the countif function as so: =COUNTIF(F2:F74;=>"2/1/2002")+COUNTIF(F2:F74;=<"2/28/2002") and get an error result for the formula. I've also tried the sumif function and can't seem to get the right configuration for the formula. Any ideas/concerns/further suggestions would be GREATLY appreciated?
    Make a list of month numbers, say in G from G2 on.

    In H2 enter:

    =SUMPRODUCT((MONTH($F$2:$F$74)=G2)+0)

    and copy down as far as needed.



    [ This Message was edited by: Aladin Akyurek on 2002-04-19 11:50 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    The following 2 alternatives work.
    Note my system is configured to use dd/mm/yy
    but not separators of ";".

    =COUNTIF(F2:F7,">=1/1/2002")-COUNTIF(F2:F74,">=31/1/2002")

    with criteria in D1 and D2

    =COUNTIF(F2:F7,">="&D1)-COUNTIF(F2:F74,">="&D2)

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your errors are caused in part by invalid relational operators...

    Use >= instead of =>
    Use <= instead of =<

    Also, the operator should be included in text string used as COUNTIF's 2nd argument.

    [ This Message was edited by: Mark W. on 2002-04-19 11:51 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 11:34, rferley wrote:
    I have a spreadsheet that has dates in one column formatted as dd/mm/yyyy and I'm trying to count the number of jan, feb, etc. occurances in this spreadsheet. I've used the countif function as so: =COUNTIF(F2:F74;=>"2/1/2002")+COUNTIF(F2:F74;=<"2/28/2002") and get an error result for the formula. I've also tried the sumif function and can't seem to get the right configuration for the formula. Any ideas/concerns/further suggestions would be GREATLY appreciated?
    use
    =COUNTIF(F2:F74,">=2/1/2002")


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for all the suggestions, here's where I'm at =COUNTIF(F2:F74,">=01/02/02")+COUNTIF(F2:F74,"<=28/02/02") the result is a number which I know to be untrue. It returns a value of 57 and I know there were only 13 occurances in Feb. The same holds true for March and April, the formula returns a number, just not an accurate one.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 13:57, rferley wrote:
    Thanks for all the suggestions, here's where I'm at =COUNTIF(F2:F74,">=01/02/02")+COUNTIF(F2:F74,"<=28/02/02") the result is a number which I know to be untrue. It returns a value of 57 and I know there were only 13 occurances in Feb. The same holds true for March and April, the formula returns a number, just not an accurate one.
    That because your formula is syntactically correct, but perhaps semantically wrong.

    I'm guessing that you think your formula is counting the entries between these dates (the intersection), but actually your formula is counting the union -- all of your dates.

    This array formula would do the trick...

    {=COUNT(IF(MONTH(F2:F74)=2,1))}


    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    [ This Message was edited by: Mark W. on 2002-04-19 14:09 ]

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 13:57, rferley wrote:
    Thanks for all the suggestions, here's where I'm at =COUNTIF(F2:F74,">=01/02/02")+COUNTIF(F2:F74,"<=28/02/02") the result is a number which I know to be untrue. It returns a value of 57 and I know there were only 13 occurances in Feb. The same holds true for March and April, the formula returns a number, just not an accurate one.
    Hi rferley:
    Depends on what you are trying to compute. The first expression computes the number of all transactions date equal to or greater than 1/2/2002 (which by the way includes all transaction dates covered by your second expression). Are you sure you want to add the two expressions? perhaps you want to subtract them -- we will know wwhether the two expressions should be added or subtracted if you provide a clear statement.

    HTH

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-19 13:57, rferley wrote:
    Thanks for all the suggestions, here's where I'm at =COUNTIF(F2:F74,">=01/02/02")+COUNTIF(F2:F74,"<=28/02/02") the result is a number which I know to be untrue. It returns a value of 57 and I know there were only 13 occurances in Feb. The same holds true for March and April, the formula returns a number, just not an accurate one.
    Since you want to do a count per month, it's better not to plug a constant in the condition/crit argument of COUNTIF (or other functions).

    Moreover, your formula should read:

    =COUNTIF(F2:F74,">=01/02/02")-COUNTIF(F2:F74,">28/02/02")

    Give also a try to what I already suggested.

    Another alternative is:

    In G2 enter and copy down:

    =MONTH(F2)

    Then use a straight COUNTIF, e.g., for February:

    =COUNTIF($G$2:$G$74,2)

    Aladin

    [ This Message was edited by: aladin akyurek on 2002-04-20 02:00 ]

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    GOTCHA! I WAS TRUELY HOPING TO COUNT, BOTH BY MONTH, AND BY THE DATE OF THAT RESPECTIVE MONTH. AHH WELL SUCH AS LIFE.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com