asked on

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?

Argenti

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

byundt

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.

Argenti

[comment removed]

eossma

ASKER

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.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eossma

ASKER

ahhh . . . this works:

=SUMIFS(C6:G42, C6:G42,"<>#DIV/0!")

Argenti

you have only ONE condition, so you can use

=SUMIF(C6:G42;"<>DIV/0!")

Open in new window

Argenti

Many thanks eossma!

byundt

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

Argenti

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