Could someone tell me what is wrong with my function here
"=SUM(IF(ISERROR(E30:E48), " ", E30:E48))"? I t returns
"#VALUE!". Thanks,

Cindy Wang wrote:

Could someone tell me what is wrong with my function here
"=SUM(IF(ISERROR(E30:E48), " ", E30:E48))"? I t returns
"#VALUE!". Thanks,

You're asking it if there's an error with the range E30:E48, when I assume
what you want is to know if there's an error with the sum of that range. Try
this formula:
=IF(ISERROR(SUM(E30:E48)),"",SUM(E30:E48))

--
I'm outta second thoughts now, anyway.

On Feb 22, 10:59*am, "Auric__" wrote:
Cindy Wang wrote:
Could someone tell me *what is wrong with my function here
"=SUM(IF(ISERROR(E30:E48), " ", E30:E48))"? * I t returns
"#VALUE!". * Thanks,

You're asking it if there's an error with the range E30:E48, when I assume
what you want is to know if there's an error with the sum of that range. Try
this formula:
* =IF(ISERROR(SUM(E30:E48)),"",SUM(E30:E48))

--
I'm outta second thoughts now, anyway.

-23285
-998
#N/A
-6474
-9406
-1715
#N/A
-12763
-1768
-628
#N/A
#N/A
-508
-2408
#N/A
#N/A
-81
-2628
-2
no, that will not work. I have used vlookup and some cell returned as
#N/A, and I try to sum those cells with value. I used your function
and it did not return anything at all.

"Cindy Wang" wrote:
Could someone tell me what is wrong with my function here
"=SUM(IF(ISERROR(E30:E48), " ", E30:E48))"? I t returns
"#VALUE!".

I presume you want the sum of all cells that do not contain an error.

Your formula is correct. But you just need to "array enter" it. That is,
press ctrl+shift+Enter instead of just Enter.

With the normal-entered formula already in the cell, select the cell, press
F2, then press ctrl+shift+Enter instead of just Enter.

An array-entered formula will appear in the Formula Bar surrounded by curly
braces, i.e. {=SUM(...)}. You cannot type the curly braces yourself. That
is just how Excel differentiates an array-entered formula from a
normal-entered formula.

PS: Get in the habit of typing the null string ("") instead of a string
with one space (" "). That will help you when you want to test if a cell
__appears__ blank.

hi,

formula array to validate with ctrl + shift + enter

=SUMPRODUCT(--IF(ISERROR(A2:A20),0,(A2:A20)),--NOT(ISERROR(A2:A20)))


--
isabelle



Le 2012-02-22 11:29, Cindy Wang a écrit :

-23285
-998
#N/A
-6474
-9406
-1715
#N/A
-12763
-1768
-628
#N/A
#N/A
-508
-2408
#N/A
#N/A
-81
-2628
-2
no, that will not work. I have used vlookup and some cell returned as
#N/A, and I try to sum those cells with value. I used your function
and it did not return anything at all.

One more...

=SUMIF(E30:E48,"<#N/A")
(not an array formula)
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(commercial and free excel programs)




"Cindy Wang"
wrote in message ...
Could someone tell me what is wrong with my function here
"=SUM(IF(ISERROR(E30:E48), " ", E30:E48))"? I t returns
"#VALUE!". Thanks,

correction:

array formula to validate with ctrl + shift + enter

=SUMPRODUCT(--IF(ISERROR(A2:A20),0,(A2:A20)))


--
isabelle



Le 2012-02-22 13:34, isabelle a écrit :
hi,

formula array to validate with ctrl + shift + enter

=SUMPRODUCT(--IF(ISERROR(A2:A20),0,(A2:A20)),--NOT(ISERROR(A2:A20)))


--
isabelle