excel formula of sum with error and cells not in range

You can use the AGGREGATE function to SUM and ignore errors:

=AGGREGATE(9,6,c3,f3,h3,j3,n3)

You can use IFERROR to do an alternative for an error, in your example the alternative is 0.

So you could write it as =IFERROR(C3, 0) + IFERROR(F3, 0) + So on....

Is the formula you're looking for something like this?

= SUMIF (A1: A14; "<> # N / A")


= SUM (IF(ISERROR(A1: D2), "", A1: D2))

SUM on its own does not ignore nested subtotals, hidden rows and can’t cope with error values. SUBTOTAL using function 9 (SUM) ignores nested subtotals but returns an error when the range it is calculating on includes an error value. SUBTOTAL using function 109 does the same but does ignore hidden rows (row 9 is hidden). The AGGREGATE function, again using SUM function 9, and using option 3 (see below) ignores everything the SUBTOTAL 109 ignores but also ignores errors.The last 6 functions: VAR.P, MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC , QUARTILE.INC, PERCENTILE.EXC and QUARTILE.EXC require a k value as a fourth argument. In the example below in row 16, the LARGE function is used with a k value of 2. This returns the second largest value in the range. Row 17 uses the SMALL function with a k value of 3 – the third smallest value.