How to SUM #N/A

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Looking-for-a-brainiac - 19 Jun 2006 16:30 GMT

I have VLOOKUPs that return the value #N/A on occasion, as a matter or
course. I SUM those values. Currently I copy/paste values on the results,
then sort out and delete the #N/As to make the SUM work.

Is there a better way?

Can you alter the SUM sytax to force it to skip over #N/As?
Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as
zero?

Reply to this Message

VBA Noob - 19 Jun 2006 16:43 GMT

How about this array

Paste formula into cell then press Ctrl + shift + enter

=SUM(IF(ISERROR(AA5:AA350),"",AA5:AA350))

Signature

VBA Noob

Reply to this Message

kempo - 19 Jun 2006 16:51 GMT

hi,

you can imbed in the if by using the iserror function, so you would
have =IF(ISERROR(A1),0,A1) for example.

let me know if have any problems

> I have VLOOKUPs that return the value #N/A on occasion, as a matter or
> course. I SUM those values. Currently I copy/paste values on the results,
[quoted text clipped - 5 lines]
> Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as
> zero?

Reply to this Message

Looking-for-a-brainiac - 19 Jun 2006 17:30 GMT

Got it -- it works. THANKS fellas!

> hi,
>
[quoted text clipped - 12 lines]
> > Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as
> > zero?

Reply to this Message

Aladin Akyurek - 26 Jun 2006 07:00 GMT

=SUM(SUMIF(Range,{"<0",">0"}))

Reply to this Message

Jay - 27 Jun 2006 10:03 GMT

Or - the second option you mentioned (IF..)

=IF(ISNA(VLOOKUP(A1,range,col,index)),"0",VLOOKUP(A1,range,col,index))

Which will return a zero instead of an #N/A.

The statement checks for an N/A error and returns a 0.

Help search on ISNA for more details.

HTH

-Jay-

On 26/6/06 07:00, in article 449f77ee$0$31644$e4fe514c@news.xs4all.nl,

> =SUM(SUMIF(Range,{"<0",">0"}))
>
[quoted text clipped - 7 lines]
>> Or can you imbed an IF/THEN statement arount the VLOOKUP to display #N/As as
>> zero?

Reply to this Message

Franz Verga - 27 Jun 2006 10:17 GMT

Nel post news:C0C6B2EF.393A%zeugma@toucanspam.com
*Jay* ha scritto:

> Or - the second option you mentioned (IF..)
>
[quoted text clipped - 9 lines]
>
> -Jay-

No Jay, your formula will not return a zero instead of an #N/A, but a string
which has just one character 0, so should be better write 0 without quotes:
quotes are needed just for text, not for numbers.

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Jay - 27 Jun 2006 15:00 GMT

On 27/6/06 10:17, in article uvuSKqcmGHA.4164@TK2MSFTNGP03.phx.gbl, "Franz
Verga" <fra68ve@InVento.it> wrote:

> Nel post news:C0C6B2EF.393A%zeugma@toucanspam.com
> *Jay* ha scritto:
[quoted text clipped - 16 lines]
> which has just one character 0, so should be better write 0 without quotes:
> quotes are needed just for text, not for numbers.

Thanks Franz, I didn't realise I'd done that - (wrote it without thinking).

Am I correct in thinking the 0 string could still have been recognised as a
number though, for the SUM?

-Jay-

Reply to this Message

Franz Verga - 27 Jun 2006 15:17 GMT

Nel post news:C0C6F88B.39C8%zeugma@toucanspam.com
*Jay* ha scritto:

> On 27/6/06 10:17, in article uvuSKqcmGHA.4164@TK2MSFTNGP03.phx.gbl,
>
[quoted text clipped - 26 lines]
>
> -Jay-

No, it's note recognized, just skipped like blanks or null strings, also
words: you can try this;
input: A1 ==> 56, A2 ==> ="", A3 ==> ="0", A4 ==> mouse, A5 ==> 21 A6 ==>
=SUM(A1:A5)

The result in A6 is 77...

Signature

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Jay - 27 Jun 2006 16:24 GMT

On 27/6/06 15:17, in article #k#BvRfmGHA.3468@TK2MSFTNGP03.phx.gbl, "Franz
Verga" <fra68ve@InVento.it> wrote:

> Nel post news:C0C6F88B.39C8%zeugma@toucanspam.com
> *Jay* ha scritto:
[quoted text clipped - 36 lines]
>
> The result in A6 is 77...

Well it would be *even* if the 0 string was recognised as a number, with it
being zero, but I know what you mean - 'Number' Text strings aren't
recognised as numbersand skipped like nulls. However, can you explain this:

A4 ==> 7
A5 ==> "2"

SUM(A4:A5) gives 7 (As expected,the "2" string isn't recognised as a num)

=A4*A5 gives 14
=SUM(A4*A5) gives 14

Why is the "2" text string not recognised as a number by the function, but
*is* when used in a direct mathematical operation, A4*A5 ?

-Jay-

Reply to this Message

Franz Verga - 27 Jun 2006 17:21 GMT

Nel post news:C0C70C2A.39E7%zeugma@toucanspam.com
*Jay* ha scritto:

> On 27/6/06 15:17, in article #k#BvRfmGHA.3468@TK2MSFTNGP03.phx.gbl,
>
[quoted text clipped - 58 lines]
>
> -Jay-

Because Excel do an implicit conversion, when you use strings as arguments
in calculation Excel try to "translate" the strings in numbers, but the
function don't try to do this translation, because they don't have the
values , just the references, so they use the range as they are. But if you
put a string inside the SUM function you will be surprised, because Excel
now do the translation, so, for example =SUM("22",5) returns 27.

Also if you input A1 ==> =5<6, A2 ==> 6 and the try to sum, if you do:
=SUM(A1:A2) the results would be 6, but if you do =A1+A2 you will have 7 and
also if you do SUM(5<6,6) you will have 7.

This is because generally speaking functions inputs are references, not
values, but if you input values in a function, the function would try to
translate that value in a number.

Signature

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message