SUM ONLY CELLS INA RANGE THAT ARE NUMBERS

[R. Arizpe]

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

 

[Jarek Kujawa]

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

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

 

[Jarek Kujawa]

another way:

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

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

 

[Bob Phillips]

=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)

 

[Dave Peterson]

One more:

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

10E37 is a huge number in scientific notation.

 

[R. Arizpe]

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

 

[R. Arizpe]

Seems like both formulas work, thank you!!