SUM ONLY CELLS INA RANGE THAT ARE NUMBERS
 

Given the next example:

COLUMN A
ROW1 27.35
ROW2 112.01
ROW3 3.75
ROW4 #N/A
ROW5 4.55
ROW6 #N/A
ROW7

I want to enter a formula on cell A7 that will find all
of the cells that ARE NUMBERS, and then return the SUM
of those cells.

I asked this before, but making comparisons vs. zero or
vs. very large negative numbers does not work.

Thank you

Answer: SUM ONLY CELLS INA RANGE THAT ARE NUMBERS
 

To sum only cells in a range that are numbers, use the SUMIF function in Excel:

1. Select the cell where you want to display the sum of the numbers, for example, A7.
2. Type the formula
   Code:

   ---

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

   ---

   in the formula bar.
3. Press Enter.

This formula will sum all the cells in the range A1:A6 that are not equal to "#N/A". The result will be displayed in cell A7.

If you have other non-numeric values in the range, you can modify the formula to exclude them as well. For example, if you have blank cells or cells with text in the range, you can use the following formula:

This formula subtracts the sum of the blank cells and cells with text from the total sum of the range.

SUM ONLY CELLS INA RANGE THAT ARE NUMBERS
 

=SUM(IF(ISNUMBER(A1:A6),A1:A6,0))

this is an array formula so insert it using CTRL+SHIFT+ENTER

SUM ONLY CELLS INA RANGE THAT ARE NUMBERS
 

another way:

=SUM(IF(ISERROR(A1:A6),0,A1:A6))

this is an array formula so insert it using CTRL+SHIFT+ENTER

SUM ONLY CELLS INA RANGE THAT ARE NUMBERS
 

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

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)

"R. Arizpe" wrote in message
...
Given the next example:

COLUMN A
ROW1 27.35
ROW2 112.01
ROW3 3.75
ROW4 #N/A
ROW5 4.55
ROW6 #N/A
ROW7

I want to enter a formula on cell A7 that will find all
of the cells that ARE NUMBERS, and then return the SUM
of those cells.

I asked this before, but making comparisons vs. zero or
vs. very large negative numbers does not work.

Thank you

SUM ONLY CELLS INA RANGE THAT ARE NUMBERS
 

One mo

=SUMIF(A:A,"<10E37")

10E37 is a huge number in scientific notation.



R. Arizpe wrote:

Given the next example:

COLUMN A
ROW1 27.35
ROW2 112.01
ROW3 3.75
ROW4 #N/A
ROW5 4.55
ROW6 #N/A
ROW7

I want to enter a formula on cell A7 that will find all
of the cells that ARE NUMBERS, and then return the SUM
of those cells.

I asked this before, but making comparisons vs. zero or
vs. very large negative numbers does not work.

Thank you

--

Dave Peterson

SUM ONLY CELLS INA RANGE THAT ARE NUMBERS
 

Thanks, both your post and Jarek's work fine, all I had to do is enter the
correct range and the function names in Spanish (that is the excel version I
have).

"Bob Phillips" wrote:

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

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)

"R. Arizpe" wrote in message
...
Given the next example:

COLUMN A
ROW1 27.35
ROW2 112.01
ROW3 3.75
ROW4 #N/A
ROW5 4.55
ROW6 #N/A
ROW7

I want to enter a formula on cell A7 that will find all
of the cells that ARE NUMBERS, and then return the SUM
of those cells.

I asked this before, but making comparisons vs. zero or
vs. very large negative numbers does not work.

Thank you

SUM ONLY CELLS INA RANGE THAT ARE NUMBERS
 

Seems like both formulas work, thank you!!

"Jarek Kujawa" wrote:

another way:

=SUM(IF(ISERROR(A1:A6),0,A1:A6))

this is an array formula so insert it using CTRL+SHIFT+ENTER