Semi Urgent- trying to sum and getting #N/A
Posted by Chrissy on January 08, 2001 3:37 PM
I have a row of numbers to add up. When i sum them it always returns #N/A. This happens even if i paste the numbers into a different spreadsheet etc. I am very confused. Any ideas?
Posted by Mark W. on January 08, 2001 3:41 PM
Use =SUMIF(range,"<>#N/A") where "range" is
a valid worksheet cell range.
Posted by Chrissy on January 08, 2001 4:00 PM
I tried that and it returned a number, but the number returned was higher than if i add up the cells manually. I am so confused. I checked and there is nothing hidden.
Thanx for the suggestion
Posted by Mark W. on January 08, 2001 4:23 PM
By "manually" do you mean with a calculator? Now
you really have my curiosity up. Do this:
1. Select a blank cell and type an equal sign (=).
2. Click on the 1st cell in your range.
3. Shift-click on the last cell in your range.
4. Paste the formula you just entered in a followup
to this posting.
5. With this new cell still selected, click on the
formula bar; type Ctrl+=; and paste the resultant
array constant into the same followup posting.
Posted by Chrissy on January 08, 2001 4:45 PM
Ok, please remember that i am not Excel whizz and might well be doing something daft here :-). It is cool to be able to get help like this. I'm a little confused as to how to post it here ...
=Q177:Q433 is the range
=SUMIF(Q177:Q433,"<>#n/a")was the formula i used
Posted by Mark W. on January 08, 2001 4:52 PM
Okay, but if you enter =Q177:Q433 into an empty cell...
then click on the formula bar and type [Ctrl]+=. Excel
will display an array constant in the formula bar.
Copy that array constant and paste it into a reply
to this posting.
Posted by Chrissy on January 08, 2001 5:01 PM
Ahhh
ok, i tried that, but it said 'forumla was too long'
Posted by Mark W. on January 08, 2001 5:08 PM
Darn it! I was afraid that would happen. Okay, try
this:
1. Select all the cells in your range
2. Type Ctrl+G
3. Press the "Special..." button
4. Click the "Constants" radio button
5. Uncheck all the check boxes EXCEPT "Errors"
6. Press OK
7. If Excel says, "No cells were found.", repeat
steps 1-3, click the "Formulas" radio button at
step 4, then repeat step 5-6
The cell address with the error is listed just the
the left of the formula bar.
Posted by Chrissy on January 08, 2001 5:27 PM
ARRGHH
your very first formula worked fine, there was just a number that the database had pasted funny.
Pure genius
thank you very very much
Chrissy
