vlookup ignoring empty cells

[art]

Hello:

I would like to add two amount by using the vlookup, however one might be
empty, which causes the vlookup to return #Value error. How can I check if
one cell is empty then dont add that cell.

Lets say : A B C D
1 John 600 Mark
2 Mark 550 Jones
3 Marry 650
4 Jones 645

And in cell A6 I should have the formula
=(vlookup(D1,EmpList,2)+Vlookup(D2,EmpList,2)+Vlookup(D3,EmpList,2)+Vlookup(D4,EmpList,2)

It should return the total amount and ignoring the cell that are empty.

Thanks.

Art

 

[Gary''s Student]

instead of =VLOOKUP()
use:
=IF(ISERROR(VLOOKUP()),0,VLOOKUP())

 

[art]

Thanks. But is there a way to just check if the cell is empty then dont do
anything. Because Your way, I have to enter the formula Vlookup twice (if
vlookup is error then zero, if not the vlookup). Is there a way to do this by
entering only once the vlookup formula?

Thanks

Art

 

[Max]

One way. Place the 4 vlookups, each in its own cell within a contiguous
range, say in A2:A5. Then you could use this expression in A6 to sum it up,
which will ignore the possible errors or text returned by any of the 4
vlookups:
=SUM(IF(ISNUMBER(A2:A5),A2:A5))

P/s: I'd think that you should review your vlookups to use an exact match,
viz: instead of: vlookup(D1,EmpList,2)
use: vlookup(D1,EmpList,2,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Max]

=SUM(IF(ISNUMBER(A2:A5),A2:A5))
Missed out mentioning earlier that the expression above needs to be
array-entered, ie confirmed via pressing CTRL+SHIFT+ENTER (instead of just
ENTER)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

 

[art]

If the first vlookup returns an error, it doesn't calculate anymore, which is
not what I want. If the first vlookup returns anm error, it should go to the
next vlookup.

Thanks.

 

[T. Valko]

Try this:

=SUMPRODUCT(SUMIF(A1:A4,D14,B1:B4))