Convert #N/A to number

[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

 

[Chip Pearson]

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

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'

 

[Guest]

Thanks, I couldn't use your formula exactly, but I found a solution to my problem thanks to your reply!

Thanks again

John