# Thread: #N/A's in cells (Excel 2000)

1. ## #N/A's in cells (Excel 2000)

Hi,

I've linked a couple of spreadsheets and have got alot of #N/A's in certain cells. I'm trying to add all of the column together but because of the #N/A's these aren't totalling, is there any way around this problem?

Thanks for any help
Andy

2. ## Re: #N/A's in cells (Excel 2000)

Welcome to Woody's Lounge!

The following formula, entered as an array formula (i.e. confirmed with Ctrl+Shift+Enter instead of just Enter), will add all numeric entries in A1:A1000:

=SUM(IF(ISNUMBER(A1:A1000),A1:A1000))

3. ## Re: #N/A's in cells (Excel 2000)

Hi Hans,

Thanks for the quick reply! I've just tried that formula but I just end up with 0 in the total for some reason?

Thanks again
Andy

4. ## Re: #N/A's in cells (Excel 2000)

1) did you make sure you confirmed with ctrl-shift-enter not just enter?

2) are you sure they are numbers and not text that looks like numbers?

Steve

5. ## Re: #N/A's in cells (Excel 2000)

Thanks Steve - it was the ctrl-shift-enter bit i'd not done - all ok now.

Andy

6. ## Re: #N/A's in cells (Excel 2000)

Another way is to use the ISNA function, ie replace the formula that is causing the #N/As - [yours], in my example below - with the following:

=if(ISNA([yours]),0,[yours])

That way, if the result *would* have been #N/A it's replaced with a zero.

HTH

7. ## Re: #N/A's in cells (Excel 2000)

Try,

=SUMIF(A1:A100,"<>#N/A")

or rid the cells of the #N/A and just use SUM.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•