Page 1 of 3 123 LastLast
Results 1 to 10 of 21

=countdiff

This is a discussion on =countdiff within the Excel Questions forums, part of the Question Forums category; I have a named range HomeWin =Sheet1!$C$6:$C$28 So, what am I doing wrong in A4 to get a return of ...

  1. #1
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,393

    Default =countdiff

    I have a named range HomeWin =Sheet1!$C$6:$C$28

    So, what am I doing wrong in A4 to get a return of 9 and not 8 please...

    Sheet1

     ABC
    4918
    5 1Barnsley
    6 1West Bromwich Albion
    7 2Ipswich Town
    8 3Cardiff City
    9 4Middlesbrough
    10 5Leicester City
    11 6Watford
    12 7Blackpool
    13 8Nottingham Forest
    14 9 
    15 10 
    16 11 
    17 12 
    18 13 
    19 14 
    20 15 
    21 16 
    22 17 
    23 18 
    24 19 
    25 20 
    26 21 
    27 22 
    28 23 

    Spreadsheet Formulas
    CellFormula
    A4=COUNTDIFF(INDEX(HomeWin,0,B4))
    B4=MATCH(D1,Headers,0)
    C4=SUMPRODUCT((Home=C5)*(FT=$D$2))
    B5=MATCH(C5,Headers,0)
    C5{=TRANSPOSE(Fixtures!$N$4:$N$27)}
    C6{=IF(ROWS(C$6:C6)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C6))),"")}
    C7{=IF(ROWS(C$6:C7)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C7))),"")}
    C8{=IF(ROWS(C$6:C8)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C8))),"")}
    C9{=IF(ROWS(C$6:C9)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C9))),"")}
    C10{=IF(ROWS(C$6:C10)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C10))),"")}
    C11{=IF(ROWS(C$6:C11)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C11))),"")}
    C12{=IF(ROWS(C$6:C12)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C12))),"")}
    C13{=IF(ROWS(C$6:C13)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C13))),"")}
    C14{=IF(ROWS(C$6:C14)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C14))),"")}
    C15{=IF(ROWS(C$6:C15)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C15))),"")}
    C16{=IF(ROWS(C$6:C16)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C16))),"")}
    C17{=IF(ROWS(C$6:C17)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C17))),"")}
    C18{=IF(ROWS(C$6:C18)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C18))),"")}
    C19{=IF(ROWS(C$6:C19)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C19))),"")}
    C20{=IF(ROWS(C$6:C20)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C20))),"")}
    C21{=IF(ROWS(C$6:C21)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C21))),"")}
    C22{=IF(ROWS(C$6:C22)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C22))),"")}
    C23{=IF(ROWS(C$6:C23)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C23))),"")}
    C24{=IF(ROWS(C$6:C24)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C24))),"")}
    C25{=IF(ROWS(C$6:C25)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C25))),"")}
    C26{=IF(ROWS(C$6:C26)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C26))),"")}
    C27{=IF(ROWS(C$6:C27)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C27))),"")}
    C28{=IF(ROWS(C$6:C28)<=C$4,INDEX(Away,SMALL(IF(Home=C$5,IF(FT=$D$2,ROW(Away)-ROW(INDEX(Away,1,1))+1)),ROWS(C$6:C28))),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
    Xl 2003...

  2. #2
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,393

    Default Re: =countdiff

    Any ideas with this one please... I just can't see what it is not counting correctly...
    Xl 2003...

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Location
    seattle
    Posts
    394

    Default Re: =countdiff

    the thing is, i don't know what COUNTDIFF is. I've never seen it, and my version of excel doesn't have it
    I want Excel and Access to get married and have a baby. And I would call that baby 'Accel'

    Windows 7, Office 2010

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,666

    Default Re: =countdiff

    Do you maybe have any spaces in the remainder of the range which aren't in fact entirely blank? I would select all these "blank" cells, and go Edit>Clear>All in them, recaluclate the sheet if necessary (Shift+F9) and see if the result changes.
    Richard Schollar

    Using xl2013

  5. #5
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,393

    Default Re: =countdiff

    Quote Originally Posted by Richard Schollar View Post
    Do you maybe have any spaces in the remainder of the range which aren't in fact entirely blank? I would select all these "blank" cells, and go Edit>Clear>All in them, recaluclate the sheet if necessary (Shift+F9) and see if the result changes.
    Hi Richard,

    If I delete clear all from C14:C28 then A4 will show an 8... But, I can't clear the cells as in the OP Jeanie they contain formulas... So I agree, it is something to do with the "blank" cells in the range...
    Xl 2003...

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,415

    Default Re: =countdiff

    Try...

    =COUNTDIFF(INDEX(HomeWin,0,B4),,"")
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,393

    Default Re: =countdiff

    Then again, I don't get this...

    =COUNTDIFF(Array,Blanks,Exclude)

    - Blanks (boolean, optional) : specifies if empty cells must be taken into account (default : FALSE)

    I get the same return if I use...

    =COUNTDIFF(INDEX(HomeWin,0,B1),FALSE)

    =COUNTDIFF(INDEX(HomeWin,0,B1),TRUE)

    and

    =COUNTDIFF(INDEX(HomeWin,0,B1))
    Xl 2003...

  8. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,666

    Default Re: =countdiff

    Aladin posted just before you did - looks like his suggestion will sort your problem
    Richard Schollar

    Using xl2013

  9. #9
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,393

    Default Re: =countdiff

    Quote Originally Posted by Aladin Akyurek View Post
    Try...

    =COUNTDIFF(INDEX(HomeWin,0,B4),,"")
    LOL Hi Aladin...you are SO annoying at times with your correct answers...!!!

    I had tried =COUNTDIFF(INDEX(HomeWin,0,B4),"") but it didn't work...

    Your solution has now got the expected return but when I use the formula to extract, it doesn't return in the first cell where I place the formula... I get the right amount of records out because I've applied the formula to 23 cells BUT, the top cell gives no return...

    Any idea please?
    Xl 2003...

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,415

    Default Re: =countdiff

    Quote Originally Posted by How_Do_I View Post
    LOL Hi Aladin...you are SO annoying at times with your correct answers...!!!

    I had tried =COUNTDIFF(INDEX(HomeWin,0,B4),"") but it didn't work...

    Your solution has now got the expected return but when I use the formula to extract, it doesn't return in the first cell where I place the formula... I get the right amount of records out because I've applied the formula to 23 cells BUT, the top cell gives no return...

    Any idea please?
    That's the issue you have with UNIQUEVALUES in your orginal thread, right? If so, I was going to respond to that there...
    Assuming too much and qualifying too much are two faces of the same problem.

Page 1 of 3 123 LastLast

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