Re: filter out all the #n/a...

I have done a vlookup in col(b), col(c) and col(d)

i want to summarise column b,c,d in column E


e.g 

if col b2 = #na, c2=#na, d2=300 then i want e2=300

if col b3 = 100, c2=#na, d2=#na then i want e2=100



how would i do this?? some sort of OR function?

also is there a way to highlight/filter all the rows where b2=#na
c2=#na, d2=#na ? this will tell me which rows that are not found in an
of my vlookups

--
Message posted from http://www.ExcelForum.com

0
7/2/2004 6:40:08 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
425 Views

Similar Articles

[PageSpeed] 35

If you are getting #n/a as a result of the vlookup statements, tr
putting in the following formula. This will produce "Not found" instea
of #n/a where the vlookup is unsuccessful

=if(isna(<vlookupstatement>)=true,"Not  Found",<vlookupstatement>)

Your sums will then only read the number sand not the text of no
found. 

If you want to highlight all rows where not found is try formula belo
in column e

=if (and(b2="Not Found",c2="Not Found",d2="No
Found"),"Unsuccessful",sum(b2:d2)) 

Hope this is what you are looking for 

Romanian



Your post:
I have done a vlookup in col(b), col(c) and col(d)

i want to summarise column b,c,d in column E


e.g 

if col b2 = #na, c2=#na, d2=300 then i want e2=300

if col b3 = 100, c2=#na, d2=#na then i want e2=100



how would i do this?? some sort of OR function?

also is there a way to highlight/filter all the rows where b2=#na
c2=#na, d2=#na ? this will tell me which rows that are not found in an
of my vlookups

--
Message posted from http://www.ExcelForum.com

0
7/2/2004 9:27:00 AM
that works...

i have another scenario... what it the data in column b,c,d is text...

i want column e to contain the non blank data from either column b,c o
d

e.g 

if b1= blank, c1=blank, d1= "Peter Smith" -> then e1="Peter Smith"
if b2= "Roger", c2=blank, d2= blank -> then e2="Roger"


thx in advanc

--
Message posted from http://www.ExcelForum.com

0
7/3/2004 2:04:55 PM
Reply:

Similar Artilces: