# Sum a Column, but ignore fields with errors

I'm trying to add up a large column that has some fields with errors.  The errors are acceptable errors.  I want the formula to ignore the error fields and add the rest in the column.  How would I go about doing that?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Suppose your value range is C2:C99 and you have three types of errors (#DIV/0!, #VALUE! and #N/A)
Then your sum formula should be:
=SUMIFS(C2:C99;C2:C99;"<>#DIV/0!";C2:C99;"<>#VALUE!";C2:C99;"<>#N/A")

If several other error types exist, you add two more parameters to your formula: the value range and the error type, preceded by "<>", in double quotes
0
Mechanical EngineerCommented:
The following array-entered formula will add up numeric values while ignoring errors:
=SUM(IF(ISNUMBER(C2:C99),C2:C99,""))

To array-enter a formula:
1.  Click in the formula bar
2.  Hold the Control and Shift keys down
3.  Press the Enter key, then release all three keys
Excel should respond by adding curly braces { } surrounding your formula.
0
Commented:
[comment removed]
0
Author Commented:
So this is what I'm shooting for:
=SUMIFS(C6:G42;C6:G42;"<>#DIV/0!")

but I get an error "The formula you typed contains an error", and it highlights G42 in the formula.
0
Mechanical EngineerCommented:
If you have only #DIV/0! error values, then you could use:
=SUMIF(C6:G42,"<>#DIV/0!")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
ahhh . . . this works:

=SUMIFS(C6:G42, C6:G42,"<>#DIV/0!")
0
Commented:
you have only ONE condition, so you can use
=SUMIF(C6:G42;"<>DIV/0!")
0
Commented:
Many thanks eossma!
0
Mechanical EngineerCommented:
Argenti,
In your French version of Excel, would the formula be the following?
=SOMME.SI(C6:G42;"<>#DIV/0!")

I'm unclear whether the French version uses the same error value #DIV/0! as the American version. I found the hash sign # to be essential in making the formula work at my end. When you had posted using DIV/0! it made me think that the French version might be a little different.

0
Commented: