Summing all numeric values only in cells in a column
 

I guess I am spinning my wheels this morning trying different formula
combinations of Sumif with Isnumber and other combinations to sum all of the
numeric values in a column of cells containing either numbers or #N/As. My
thanks in advance for any suggestions.

Cheers! Brad

What formula would I use to sum a column of values like this?

6.19
#N/A
#N/A
2.97
#N/A
#N/A
5.22

Answer: Summing all numeric values only in cells in a column
 

Hi Brad,

To sum all the numeric values in a column of cells containing either numbers or #N/As, you can use the SUMIF function with the ISNUMBER function as the criteria.

Here are the steps to follow:

1. Select the cell where you want to display the sum.
2. Type the following formula:
   Code:

   ---

   =SUMIF(range,"0")

   ---

3. Replace "range" with the range of cells you want to sum. In your case, it would be the column of cells containing the values you provided.
4. Press Enter.

The formula will sum all the numeric values in the range and ignore any non-numeric values like #N/A.

Summing all numeric values only in cells in a column
 

=SUM(IF(ISNUMBER(A1:A100),A1:A100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Brad" wrote in message
...
I guess I am spinning my wheels this morning trying different formula
combinations of Sumif with Isnumber and other combinations to sum all of
the numeric values in a column of cells containing either numbers or #N/As.
My thanks in advance for any suggestions.

Cheers! Brad

What formula would I use to sum a column of values like this?

6.19
#N/A
#N/A
2.97
#N/A
#N/A
5.22

Summing all numeric values only in cells in a column
 

First thing that comes to mind is to get rid of the N/A errors, put this in
your formula's if(iserror(yourformula),"" or zero, yourformula)
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Brad" wrote:

I guess I am spinning my wheels this morning trying different formula
combinations of Sumif with Isnumber and other combinations to sum all of the
numeric values in a column of cells containing either numbers or #N/As. My
thanks in advance for any suggestions.

Cheers! Brad

What formula would I use to sum a column of values like this?

6.19
#N/A
#N/A
2.97
#N/A
#N/A
5.22

Summing all numeric values only in cells in a column
 

You should get rid of those errors but in the meantime

=SUMIF(A1:A100,"<#N/A")

Mike

"Brad" wrote:

I guess I am spinning my wheels this morning trying different formula
combinations of Sumif with Isnumber and other combinations to sum all of the
numeric values in a column of cells containing either numbers or #N/As. My
thanks in advance for any suggestions.

Cheers! Brad

What formula would I use to sum a column of values like this?

6.19
#N/A
#N/A
2.97
#N/A
#N/A
5.22

Summing all numeric values only in cells in a column
 

On Thu, 18 Oct 2007 09:08:28 -0500, "Brad" wrote:

I guess I am spinning my wheels this morning trying different formula
combinations of Sumif with Isnumber and other combinations to sum all of the
numeric values in a column of cells containing either numbers or #N/As. My
thanks in advance for any suggestions.

Cheers! Brad

What formula would I use to sum a column of values like this?

6.19
#N/A
#N/A
2.97
#N/A
#N/A
5.22







You could use the following as an **ARRAY** formula.

To enter an array formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula if you did it correctly.

=SUM(IF(ISNUMBER(A1:A10),A1:A10))

Note that you cannot reference the entire column using this method (e.g. you
cannot use A:A; maximum range would be A1:A65535)
--ron