# Thread: Summing Only Numbers (2000)

1. ## Summing Only Numbers (2000)

I have two "sets" of data I am trying to sum, one in cells A1:A5 and the other in cells A7:A10. I am using the formula "=SUM(A1:A5,A7:A10)" in cell A12. Unfortunately, some of the data in the two ranges include "N/A"s as opposed to numbers. What I wish to do is sum only the numerical data in both sets of data. Ideas?

2. ## Re: Summing Only Numbers (2000)

When you ue the SUM Function, it only sums up numbers. NA will not affect the result.

3. ## Re: Summing Only Numbers (2000)

What does A6 contain? If it contains text, you can sum A1:A10 (text will be ignored).

You can use this array formula (confirm with Ctrl+Shift+Enter instead of just Enter):

=SUM(IF(ISNA(A1:A5),0,A1:A5))+SUM(IF(ISNA(A7:A10), 0,A7:A10))

or, if possible

=SUM(IF(ISNA(A1:A10),0,A1:A10))

4. ## Re: Summing Only Numbers (2000)

SUM ignores text values, but not error values. #N/A means "not available". If one of the arguments is not available, the sum is also not available.

5. ## Re: Summing Only Numbers (2000)

I only considered that NA could have been #N/A after I replied. I was busy with a solution on this when I noticed your reply. Tx for that.

6. ## Re: Summing Only Numbers (2000)

You could also use the "non-Array" formula:
=SUMIF(A1:A5,"<9.99999999999999E+307") +SUMIF(A7:A10, "<9.99999999999999E+307")

Steve

7. ## Re: Summing Only Numbers (2000)

This 9.99999999999999E+307 always tends to "scare" me when I see it in formulas. It seems so out of place!

8. ## Re: Summing Only Numbers (2000)

Thanks to all.

9. ## Re: Summing Only Numbers (2000)

You could use a more "routine" number if you knew the potential range of values (<1000000) or some other large number. I just chose the largest one. Sometimes I just use ">0" if I know there will be no negative numbers.

Steve

#### Posting Permissions

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