Trying to Sum a list including errors

[tomshaw4]

Hello

I am trying to sum the following list but my formula come up as 0

AALEXAND</SPAN>	#N/A</SPAN>
ABITURNE</SPAN>	#N/A</SPAN>
ABRIGGS</SPAN>	#N/A</SPAN>
ABROADMA</SPAN>	#N/A</SPAN>
ADGILL</SPAN>	11</SPAN>
AEVANS</SPAN>	#N/A</SPAN>
AFURNEAU</SPAN>	#N/A</SPAN>
AHALL</SPAN>	#N/A</SPAN>
AHAYES</SPAN>	#N/A</SPAN>
AMACK</SPAN>	#N/A</SPAN>
AMCLARNO</SPAN>	#N/A</SPAN>
AMYW</SPAN>	#N/A</SPAN>
ARIDING</SPAN>	#N/A</SPAN>
ASATCHEL</SPAN>	#N/A</SPAN>
ASKREBSKY</SPAN>	2</SPAN>
ASTUART</SPAN>	#N/A</SPAN>
AYANNACO</SPAN>	#N/A</SPAN>
BSTURGE</SPAN>	#N/A</SPAN>
BTHOMAS</SPAN>	9</SPAN>
CAMCDONA</SPAN>	#N/A</SPAN>
CARLROBE</SPAN>	#N/A</SPAN>
CASHLEY</SPAN>	3</SPAN>
CFOSTER</SPAN>	4</SPAN>
CGREATOR</SPAN>	5</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

=SUM((IF(NOT(ISERROR(U1:U24)),(U1:U24))))

Can you advise mw what I am doing wrong please

 

[mathchick]

There's a good explanation here: excel - How do I sum a named range of values that contains errors? - Stack Overflow

 

[VoG]

That is an array formula that has to be entered using CTRL + Shift + Enter, not just Enter

Excel Workbook
	T	U	V	W
1	AALEXAND	#N/A		34
2	ABITURNE	#N/A
3	ABRIGGS	#N/A
4	ABROADMA	#N/A
5	ADGILL	11
6	AEVANS	#N/A
7	AFURNEAU	#N/A
8	AHALL	#N/A
9	AHAYES	#N/A
10	AMACK	#N/A
11	AMCLARNO	#N/A
12	AMYW	#N/A
13	ARIDING	#N/A
14	ASATCHEL	#N/A
15	ASKREBSKY	2
16	ASTUART	#N/A
17	AYANNACO	#N/A
18	BSTURGE	#N/A
19	BTHOMAS	9
20	CAMCDONA	#N/A
21	CARLROBE	#N/A
22	CASHLEY	3
23	CFOSTER	4
24	CGREATOR	5
Sheet5

 

[Akashwani]

Hi,

Does this work for you?...

Excel Workbook
	A	B	C	D	E
1	Data1	Data2		Result
2	AALEXAND	#N/A		34
3	ABITURNE	#N/A
4	ABRIGGS	#N/A
5	ABROADMA	#N/A
6	ADGILL	11
7	AEVANS	#N/A
8	AFURNEAU	#N/A
9	AHALL	#N/A
10	AHAYES	#N/A
11	AMACK	#N/A
12	AMCLARNO	#N/A
13	AMYW	#N/A
14	ARIDING	#N/A
15	ASATCHEL	#N/A
16	ASKREBSKY	2
17	ASTUART	#N/A
18	AYANNACO	#N/A
19	BSTURGE	#N/A
20	BTHOMAS	9
21	CAMCDONA	#N/A
22	CARLROBE	#N/A
23	CASHLEY	3
24	CFOSTER	4
25	CGREATOR	5
26
Sheet9

If the #N/A is the result of a formula, then perhaps you should consider dealing with that and replacing it with a 0 or blank.

I hope that helps.

Ak