| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating only non-empty cells... | Excel Worksheet Functions | |||
| Excel 2003 :Summing specific cells of a particular color in a colu | Excel Discussion (Misc queries) | |||
| Summing non contiguous cells | Excel Discussion (Misc queries) | |||
| Summing visable cells only | Excel Discussion (Misc queries) | |||
| Summing only those cells that aren't hidden | Excel Worksheet Functions | |||
Linear Mode
