Sum while getting #N/A

[hsandeep]

i want to sum a column. It also contains #N/A.
Also, i want to count the number of occurrences of values in the column (values can be alphabets, numerical or alpha-numerical) but it should not count #N/A.
How to accomplish it?
thanks

 

[taurean]

Assuming you are dealing with a range from B1 to B100
Try,
=SUM(IF(ISNA(B1:B100),0,B1:B100))
The formula needs to be ARRAY ENTERED i.e. CTRL + SHIFT + ENTER
and counting formula will be:
=COUNTIF(B1:B100,"<>#N/A")

 

[hsandeep]

Assuming you are dealing with a range from B1 to B100
Try,
=SUM(IF(ISNA(B1:B100),0,B1:B100))
The formula needs to be ARRAY ENTERED i.e. CTRL + SHIFT + ENTER
and counting formula will be:
=COUNTIF(B1:B100,"<>#N/A")

Just pls explain: what does <> really goes into meaningfully in Excel?

 

[Colin Legg]

<> means does not equal.

Here are some alternatives:

The sum (numerics only)
=SUMIF(B1:B100,"<="&9.99E+307)

The count (anything except #N/A)
=COUNTA(B1:B100)-COUNTIF(B1:B100,NA())

 

[hsandeep]

<> means does not equal.

Here are some alternatives:

The sum (numerics only)
=SUMIF(B1:B100,"<="&9.99E+307)

The count (anything except #N/A)
=COUNTA(B1:B100)-COUNTIF(B1:B100,NA())

Sir, it works. But what will happen if a alphabet is punched in B1:B100? Will this formula count this wrong entry also? I want to sum only numeric values appearing. I can clarify further, if needed.

 

[Colin Legg]

Sir, it works. But what will happen if a alphabet is punched in B1:B100? Will this formula count this wrong entry also? I want to sum only numeric values appearing. I can clarify further, if needed.

The sum formula will only sum numbers.
The count formula will count anything except for #N/A values.

Why don't you test this for yourself in your worksheet? If this is not what you want then you'll need to elaborate further and provide appropriate examples.

 

[hsandeep]

Sir,
I tried. The =COUNTA(B1:B100)-COUNTIF(B1:B100,NA()) formula is also counting blanks generated due to cells having formulas containing "". It SHOULD NOT COUNT these blank values.
Please help.
Regards,

 

[hsandeep]

The sum (numerics only)
=SUMIF(B1:B100,"<="&9.99E+307)
I had been using this formula to get the TOTAL. But when I sorted the values, TOTAL got changed!!!. The TOTAL has to remain same even after sorting. How to accomplish?