Any ideas with this one please... I just can't see what it is not counting correctly...
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 ...
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
A B C 4 9 1 8 5 1 Barnsley 6 1 West Bromwich Albion 7 2 Ipswich Town 8 3 Cardiff City 9 4 Middlesbrough 10 5 Leicester City 11 6 Watford 12 7 Blackpool 13 8 Nottingham 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
Cell Formula 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 2010
Any ideas with this one please... I just can't see what it is not counting correctly...
Xl 2010
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
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
Try...
=COUNTDIFF(INDEX(HomeWin,0,B4),,"")
Assuming too much and qualifying too much are two faces of the same problem.
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 2010
Aladin posted just before you did - looks like his suggestion will sort your problem
Richard Schollar
Using xl2013
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 2010
Like this thread? Share it with others