How to exclude errors

[Tikey]

I am trying to do a SUMPRODUCT calculation which avoids bothempty cells and error refs, without success, can anyone offer a solution please?

The range of cells I am trying to add contains both text andnumbers and I am trying to sum the total number of different values ie.


A
1 SE3038
2 SE3039
3
4 SE3038
5 SE3040
6 SE3040
7 #VALUE!
8 SE3037



The answer to the above should be 4 and it should ignore theblank cells and those that may contain an error.
I have been using the following =SUMPRODUCT((A1:A8<>"")/COUNTIF(A1:A8,A1:A8&""))which ignores the empty cells but I cannot find a way of ignoring the errors.


Any help appreciated, thankyou.
<strike></strike>

_{^{<strike>
</strike>}}
<strike></strike>

<strike></strike><strike></strike>

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-ISERROR(A1:A8),IF(1-(A1:A8=""),MATCH(A1:A8,A1:A8,0))),ROW(A1:A8)-ROW(A1)+1),1))

 

[Tikey]

Hi thanks very much for reply which worked great.
The range of cells to which this applies can frequently change, is there a way of doing this automatically or will I have to manually change the range ?

 

[Aladin Akyurek]

Hi thanks very much for reply which worked great.
The range of cells to which this applies can frequently change, is there a way of doing this automatically or will I have to manually change the range ?

Book1
	A
1	SE3038
2	SE3039
3
4	SE3038
5	SE3040
6	SE3040
7	#VALUE!
8	SE3037
Sheet1

Define Data in Formulas | Name Manager as referring to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,SEARCH("?*",Sheet1!$A:$A)))

Define Ivec in Formulas | Name Manager as referring to:

=ROW(Data)-ROW(INDEX(Data,1,1))+1

The formula we have now becomes: Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(1-ISERROR(Data),IF(1-(Data=""),MATCH(Data,Data,0))),Ivec),1))

This set up if fully dynamic.

 

[Tikey]

Thanks very much, your a star, worked great.

 

[Aladin Akyurek]

Thanks very much, your a star, worked great.

You are very welcome.