0

I want a sum formula that will ignore errors.

For example something like: =sum(c3+f3+h3+j3+n3) however, some of these cells may contain an error.

Between these cells there is data which I do not want to sum.

Please help

Improve this question

4 Answers 4

Reset to default
4

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

=AGGREGATE(9,6,c3,f3,h3,j3,n3)
Improve this answer
1
  • Much more simple than my answer !
    – PeterH
    Commented Jul 14, 2021 at 11:02
2

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

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


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

enter image description here

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.

enter image description here

Improve this answer
1
  • This one will works you get +10 ,, but the best one should ignore any kind of error as shown by @Ron Rosenfeld ,, please edit your post & try to add more formula possibly like this =SUM(IF(ISERROR(A2:A20),"",A2:A20)), will attract OP & others to vote the post ☺.
    – Rajesh Sinha
    Commented Jul 14, 2021 at 15:25
1

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

Improve this answer
1
  • thank you aggregate formula is worked.
    – uttam karna
    Commented Jul 14, 2021 at 12:20
0

Try this formula:

=SUMIF(A1:A6,">0")+SUMIF(A1:A6,"<=0")

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .