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?
eossmaAsked:
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.

ArgentiCommented:
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")

Open in new window


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
byundtMechanical 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
ArgentiCommented:
[comment removed]
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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

Start your 7-day free trial
eossmaAuthor Commented:
ahhh . . . this works:

=SUMIFS(C6:G42, C6:G42,"<>#DIV/0!")
0
ArgentiCommented:
you have only ONE condition, so you can use
=SUMIF(C6:G42;"<>DIV/0!")

Open in new window

0
ArgentiCommented:
Many thanks eossma!
0
byundtMechanical 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.

Brad
0
ArgentiCommented:
Brad (byundt),

You might be surprised, but I work on an English version of Excel.

Nevertheless, good job for acquiring the points! You've been 57 seconds faster than me to post the expected version of formula (suggested by me) instead of the array-formula that you initially proposed.

What can I say? I'm out of luck...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.