+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Baffled by the (lack of) result of a countif

  1. #1
    Forum Contributor
    Join Date
    06-16-2010
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    152

    Baffled by the (lack of) result of a countif

    Hi all

    I have a baffling problem with a Countif formula on one sheet of a multi-sheet Spread.

    Basically, it is sport related, and I have one sheet per year.

    The competitors are listed in columns A to C - A is a unique number, B is their name, and C is their team.

    In a row at the bottom of this list, I record the fastest - it contains the number of the competitor.

    In another row, to the right of the competitors, I count from the 'fastest' row. So for example If competitor 12 was fastest 5 times, it counts and returns 5.
    All very straightforward!
    Except on one sheet, it is igoring competitors 16 and 17, and returning '0' where there should be 1 in each case.
    The formula is:
    =COUNTIF($E$30:$W$30,LOOKUP(A17,$A$3:$A$29))
    When I step through using the evaluation tool, in all cases (apart from the 2 in question) it evaluates the correct number (1,2,3,4 etc...)
    Here is what I get with BOTH of the 2 cells.
    COUNTIF($E$30:$W$30,LOOKUP(A17,$A$3:$A$29))
    Evaluate
    COUNTIF($E$30:$W$30,LOOKUP(16,$A$3:$A$29)) - so far so good it's found '16' !!
    Evaluate
    COUNTIF($E$30:$W$30,4) - This is the baffling part. Where is it getting '4' from?!

    Any thoughts welcomed!

    Neil

  2. #2
    Forum Contributor
    Join Date
    06-16-2010
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    152

    Re: Baffled by the (lack of) result of a countif

    Solved - there were some erroneous characters used further down the list to try and discriminate other people who stepped in to a team and used the same competitor number!

+ 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