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 any
of my vlookups.


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

If you are getting #n/a as a result of the vlookup statements, try
putting in the following formula. This will produce "Not found" instead
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 not
found.

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

=if (and(b2="Not Found",c2="Not Found",d2="Not
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 any
of my vlookups.


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

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 or
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 advance


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