iserror function

  • Thread starter Thread starter Cindy Wang
  • Start date Start date
C

Cindy Wang

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

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))
 
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))
Click to expand...

-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 said:
Could someone tell me what is wrong with my function here
"=SUM(IF(ISERROR(E30:E48), " ", E30:E48))"? I t returns
"#VALUE!".
Click to expand...

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 :
 
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 :
 
Back
Top