+ Reply to Thread
Results 1 to 8 of 8

countif function not counting :(

  1. #1
    Registered User
    Join Date
    03-20-2008
    Posts
    9

    countif function not counting :(

    I have the following formula in one column:

    =IF('C:\[Test.xls]Tab1'!$T4="x",'[Test.xls]Tab1'!G4,"")

    This formula results in a date value being placed in each cell of this column where the source file has an "x". This is all working just fine.

    Results are being placed in Column E:

    13-Mar
    14-Mar
    14-Mar
    15-Mar
    etc.....

    Then I have a simple countif function to count those up and tell me how many of each there are. It looks like:

    =COUNTIF(E:E, H2)

    H
    Date Total
    13-Mar 0
    14-Mar 0
    15-Mar 0

    So the value of Total should be 1 for 13-Mar in this example, followed by 2 for 14-Mar, and 1 for 15-Mar.

    The CountIF seems like it's treating the resulting value in column E as the formula itself instead of the result of the formula. In fact, when I replaced 13-Mar with the formula that gives me that value, then it counts it.

    I hope that makes sense. All in all, it seems like its not counting the formula result ...

    HELP!!!!

    P.S. This is all happening in the NEW version of Excel (Office 2007), with the older version this was all working just fine.
    Last edited by fsutaylor31; 01-06-2009 at 04:13 PM.

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Are you sure the results in column E are rounded to the nearest day? In other words, the value could actually be 13-Mar 03:30:30, but only be displaying as 13-Mar.

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Your formula works perfectly for me
    Jason's idea looks good.
    Try replacing the dates with =int(your_date) in your formula and see if it helps

  4. #4
    Registered User
    Join Date
    03-20-2008
    Posts
    9

    Re:

    Yes, the results definitely match (are formatted the same way) in regard to the date values.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    =countif(e:e,"="&h2) hmmm both work anyway
    Last edited by martindwilson; 01-06-2009 at 04:18 PM.

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Does this work?

    =SUMPRODUCT((E2:E100+0=H2)+0)

  7. #7
    Registered User
    Join Date
    03-20-2008
    Posts
    9

    Re:

    None of these ideas are working. I wish I had some sort of data to give out, but this spreadsheet data is sensitive so I've had to dumb it down in this thread.

  8. #8
    Registered User
    Join Date
    03-20-2008
    Posts
    9
    All - you won't believe it, but the dates from the source spreadsheet were entered incorrectly (in '08 instead of '09s). So thats why the failure was happening!

    Arghhhhhhhhhhhhhhhhhh!

    Thanks for your help.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1