Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2006
    Location
    Sheffield, Yorkshire, United Kingdom
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNTIF gives different results in Excel 2003 (Excel 2003 v XP)

    Does anyone know why Microsoft changed the way that Excel 2003 handles the COUNTIF function in Excel 2003 compared with XP?

    For example, enter the following values one each in cells a1:a10 as follows: 1,2,3,4,5,0,0,0,0,0
    In cell C1, if you enter =COUNTIF(A:A,0) both Excel 2003 and XP give the result of 5 (there are 5 cells in column A that contain the value 0)

    In Excel XP, if you enter =COUNTIF(A:A,"<>0") the result is 5 (there are 5 cells not equal to 0 in the used range). Furthermore, you can SUM the two results to get the total number of values in the range (10), and then use that to calculate percentages etc etc. Easy, and correct.
    However, in 2003 exactly the same formula gives the result as 65,531: IT INCLUDES THE NULL VALUES OUTSIDE THE "USED RANGE", even though they haven't been "dirtied". Why, why, why?

    Surely the XP version is "more correct", as one is always only interested in the data within the used range?
    The workaround would be to restrict my formula to only the used range rather than the whole column, but what about opening worksheets in Excel 2003 that use this function and were written in XP: the spreadsheets will be rendered meaningless, unless I spot and correct the change.

    Does anyone know why MS have made such a fundamental change to a function that is so old???
    Can I make Excel 2003 mimic the operation of XP?
    Are there any other basic function changes that I should watch out for?

    Any help would be appreciated, please

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF gives different results in Excel 2003 (Excel 2003 v XP)

    Welcome to Woody's Lounge!

    The problem with the old version was that the results were very confusing if you tried to count blank cells: the formula
    <code>
    =COUNTIF(A120,"")
    </code>
    can yield different results depending on the size of the used range of the worksheet, even if the number of blank cells in A120 is the same.

    You could use this custom function to emulate the "old" COUNTIF function:
    <code>
    Function OldCountIf(oRange As Range, strCondition As String) As Long
    If Not Intersect(oRange.Parent.UsedRange, oRange) Is Nothing Then
    OldCountIf = Application.WorksheetFunction.CountIf( _
    Intersect(oRange.Parent.UsedRange, oRange), strCondition)
    End If
    End Function
    </code>
    You can use it the same way as COUNTIF:
    <code>
    =CountIfOld(A:A,"<>0")
    </code>
    If you put the function in your Personal.xls macro workbook, use
    <code>
    =Personal.xls!CountIfOld(A:A,"<>0")
    </code>
    (See Legare Coleman's Personal.xls Tutorial (All) if you need more info)

  3. #3
    New Lounger
    Join Date
    Feb 2006
    Location
    Sheffield, Yorkshire, United Kingdom
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF gives different results in Excel 2003 (Excel 2003 v XP)

    Thanks for the explanation, which was very clear and helpful- I hadn't noticed that effect previously as I only use COUNTIF on whole columns rather than specific ranges.
    The "new" (2003) COUNTIF is definitely an improvement when you enter your example formulae, but has unfavourable side-effect results for mine.
    Looks like I'll just have to watch out for odd looking results when I use it, as I doubt I'll be able to change MS's mind.
    Thanks again

Posting Permissions

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