=countdiff

[How_Do_I]

I have a named range HomeWin =Sheet1!$C$6:$C$28
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
So, what am I doing wrong in A4 to get a return of 9 and not 8 please...

Excel Workbook
	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
Sheet1

 

[How_Do_I]

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

 

[swinglow]

the thing is, i don't know what COUNTDIFF is. I've never seen it, and my version of excel doesn't have it

 

[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.

 

[How_Do_I]

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

 

[Aladin Akyurek]

Try...

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

 

[How_Do_I]

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

 

[Richard Schollar]

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

 

[How_Do_I]

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?

 

[Aladin Akyurek]

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