Convert #N/A to number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I like to sum a couple of rows / collumns, but in a number of cells I have the value #N/A, so I can't sum. This #N/A is the result of other formula's (index). Is it possible to convert the #N/A to a number, so I can sum? I tried error.type (converts #N/A to 7, but then all the other cells show #N/A). Or to make the index formula show a number instead of #N/A when a value is 'not availeble'

Thanks,

John
 
John,

Try the following array formula

=SUM(IF(ISERROR(A1:A10),FALSE,A1:A10))

This is an array formula, so you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this correctly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



John said:
I like to sum a couple of rows / collumns, but in a number of
Click to expand...
cells I have the value #N/A, so I can't sum. This #N/A is the
result of other formula's (index). Is it possible to convert the
#N/A to a number, so I can sum? I tried error.type (converts #N/A
to 7, but then all the other cells show #N/A). Or to make the
index formula show a number instead of #N/A when a value is 'not
availeble'
 
Thanks, I couldn't use your formula exactly, but I found a solution to my problem thanks to your reply!

Thanks again

John
 
Back
Top