Summing Cells with #N/A
 

I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting. However, I
would like to have a sum of the columns that I have used this conditional
formatting with.

I originally did not use conditional formatting. I was trying to create an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this for
someone who is quite illiterate and they want to view the invoice "cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.

Summing Cells with #N/A
 

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joan NYC" wrote in message
...
I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting. However,
I
would like to have a sum of the columns that I have used this conditional
formatting with.

I originally did not use conditional formatting. I was trying to create
an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this for
someone who is quite illiterate and they want to view the invoice
"cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.

Summing Cells with #N/A
 

Bob's answer is an easy solution.

Another method is to do a trick on the VLOOKUP.

=if(isna(vlookup(....),"",vlookup(.....))

This puts a blank in the cell and it works quite well.

Just another solution and then you can do the sum().


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joan NYC" wrote in message
...
I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting. However,
I
would like to have a sum of the columns that I have used this conditional
formatting with.

I originally did not use conditional formatting. I was trying to create
an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this for
someone who is quite illiterate and they want to view the invoice
"cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.

Summing Cells with #N/A
 

Hi Joan

In my opinion, you would be better off getting rid of the #N/A's, rather
than trying to work around them.

Amend your Vlookup formula to either
=IF(ISNA(your_formula),"",your_formula)
or
=IF(ISERROR(your_formula),"",your_formula)

replace your_formula with your existing vlookup(.....)



--
Regards

Roger Govier


"Joan NYC" wrote in message
...
I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting.
However, I
would like to have a sum of the columns that I have used this
conditional
formatting with.

I originally did not use conditional formatting. I was trying to
create an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this
for
someone who is quite illiterate and they want to view the invoice
"cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.

Summing Cells with #N/A
 

Thanks for all the great solutions

I was originally trying to get rid of the #n/as altogether. The only thing
I could think of was to ammend by VLOOKUP with an "If.. IsBlank" situation
which did not work....

"Roger Govier" wrote:

Hi Joan

In my opinion, you would be better off getting rid of the #N/A's, rather
than trying to work around them.

Amend your Vlookup formula to either
=IF(ISNA(your_formula),"",your_formula)
or
=IF(ISERROR(your_formula),"",your_formula)

replace your_formula with your existing vlookup(.....)



--
Regards

Roger Govier


"Joan NYC" wrote in message
...
I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting.
However, I
would like to have a sum of the columns that I have used this
conditional
formatting with.

I originally did not use conditional formatting. I was trying to
create an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this
for
someone who is quite illiterate and they want to view the invoice
"cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.

Summing Cells with #N/A
 

Just to make sure .... is this what you mean:

=IF(ISNA,(VLOOKUP(I18,$B$11:$C$20,2),"",VLOOKUP(I1 8,$B$11:$C$20,2)

Bear in mind I am a couple of baby steps above novice with worksheets!

"Roger Govier" wrote:

Hi Joan

In my opinion, you would be better off getting rid of the #N/A's, rather
than trying to work around them.

Amend your Vlookup formula to either
=IF(ISNA(your_formula),"",your_formula)
or
=IF(ISERROR(your_formula),"",your_formula)

replace your_formula with your existing vlookup(.....)



--
Regards

Roger Govier


"Joan NYC" wrote in message
...
I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting.
However, I
would like to have a sum of the columns that I have used this
conditional
formatting with.

I originally did not use conditional formatting. I was trying to
create an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this
for
someone who is quite illiterate and they want to view the invoice
"cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.

Summing Cells with #N/A
 

Remove comma after the ISNA and add 0 (False) at the end of VLOOKUP. This
will take care all the #N/A issue

=IF(ISNA(VLOOKUP(I18,$B$11:$C$20,2,0),"",VLOOKUP(I 18,$B$11:$C$20,2,0)


"Joan NYC" wrote:

Just to make sure .... is this what you mean:

=IF(ISNA,(VLOOKUP(I18,$B$11:$C$20,2),"",VLOOKUP(I1 8,$B$11:$C$20,2)

Bear in mind I am a couple of baby steps above novice with worksheets!

"Roger Govier" wrote:

Hi Joan

In my opinion, you would be better off getting rid of the #N/A's, rather
than trying to work around them.

Amend your Vlookup formula to either
=IF(ISNA(your_formula),"",your_formula)
or
=IF(ISERROR(your_formula),"",your_formula)

replace your_formula with your existing vlookup(.....)



--
Regards

Roger Govier


"Joan NYC" wrote in message
...
I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting.
However, I
would like to have a sum of the columns that I have used this
conditional
formatting with.

I originally did not use conditional formatting. I was trying to
create an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this
for
someone who is quite illiterate and they want to view the invoice
"cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.

Summing Cells with #N/A
 

Thanks much

I will try it

"Teethless mama" wrote:

Remove comma after the ISNA and add 0 (False) at the end of VLOOKUP. This
will take care all the #N/A issue

=IF(ISNA(VLOOKUP(I18,$B$11:$C$20,2,0),"",VLOOKUP(I 18,$B$11:$C$20,2,0)


"Joan NYC" wrote:

Just to make sure .... is this what you mean:

=IF(ISNA,(VLOOKUP(I18,$B$11:$C$20,2),"",VLOOKUP(I1 8,$B$11:$C$20,2)

Bear in mind I am a couple of baby steps above novice with worksheets!

"Roger Govier" wrote:

Hi Joan

In my opinion, you would be better off getting rid of the #N/A's, rather
than trying to work around them.

Amend your Vlookup formula to either
=IF(ISNA(your_formula),"",your_formula)
or
=IF(ISERROR(your_formula),"",your_formula)

replace your_formula with your existing vlookup(.....)



--
Regards

Roger Govier


"Joan NYC" wrote in message
...
I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting.
However, I
would like to have a sum of the columns that I have used this
conditional
formatting with.

I originally did not use conditional formatting. I was trying to
create an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this
for
someone who is quite illiterate and they want to view the invoice
"cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.

Summing Cells with #N/A
 

All these answers were great!
Thanks for your assistance!

"Martin Fishlock" wrote:

Bob's answer is an easy solution.

Another method is to do a trick on the VLOOKUP.

=if(isna(vlookup(....),"",vlookup(.....))

This puts a blank in the cell and it works quite well.

Just another solution and then you can do the sum().


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joan NYC" wrote in message
...
I am working on an invoice that is using VLOOKUP

I have surpressed viewing the #N/A with conditional formatting. However,
I
would like to have a sum of the columns that I have used this conditional
formatting with.

I originally did not use conditional formatting. I was trying to create
an
"ISBLANK" formula which I could not get to work.

I know how to surpress the #N/As in printing only but I am doing this for
someone who is quite illiterate and they want to view the invoice
"cleanly"

Any suggestions would be appreciated. Thanks. Happy New Year.