# =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. ## =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

 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

 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

2. ## Re: =countdiff

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

3. ## 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

4. ## 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.

5. ## Re: =countdiff

Originally Posted by Richard Schollar
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...

6. ## Re: =countdiff

Try...

=COUNTDIFF(INDEX(HomeWin,0,B4),,"")

7. ## 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))

8. ## Re: =countdiff

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

9. ## Re: =countdiff

Originally Posted by Aladin Akyurek
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...

10. ## Re: =countdiff

Originally Posted by How_Do_I
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...

That's the issue you have with UNIQUEVALUES in your orginal thread, right? If so, I was going to respond to that there...

Page 1 of 3 123 Last

#### Posting Permissions

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