Sum Vlookup where there are blanks

[KneeDown2Up]

Hi

Excel 2002 sp3.

I have a sheet which has 7 tables (one per day of the week) in which a
number of members of staff are listed. Not all the staff are in all of the
days. I have a summary table at the bottom which looks up the name in each
table and sums their totals. However, I get the infamous #NA where the lookup
can't match the criteria. How can I avoid this?

This is what I have (which works when the referrence cell is occupied);-


=IF(ISERROR(SUM(VLOOKUP(B115,mon1,4,FALSE),VLOOKUP(B115,tue1,4,FALSE),VLOOKUP(B115,weds1,4,FALSE),VLOOKUP(B115,thur1,4,FALSE),VLOOKUP(B115,fri1,4,FALSE),VLOOKUP(B115,sat1,4,FALSE),VLOOKUP(B115,sun1,4,FALSE))),"",SUM(VLOOKUP(B115,mon1,4,FALSE),VLOOKUP(B115,tue1,4,FALSE),VLOOKUP(B115,weds1,4,FALSE),VLOOKUP(B115,thur1,4,FALSE),VLOOKUP(B115,fri1,4,FALSE),VLOOKUP(B115,sat1,4,FALSE),VLOOKUP(B115,sun1,4,FALSE)))

Many thanks in advance.

 

[JLatham]

Try this formula instead
=SUM(IF(ISNA(VLOOKUP(B115,mon1,4,FALSE)),0,VLOOKUP(B115,mon1,4,FALSE)),IF(ISNA(VLOOKUP(B115,tue1,4,FALSE)),0,VLOOKUP(B115,tue1,4,FALSE)),IF(ISNA(VLOOKUP(B115,weds1,4,FALSE)),0,VLOOKUP(B115,weds1,4,FALSE)),IF(ISNA(VLOOKUP(B115,thur1,4,FALSE)),0,VLOOKUP(B115,thur1,4,FALSE)),IF(ISNA(VLOOKUP(B115,fri1,4,FALSE)),0,VLOOKUP(B115,fri1,4,FALSE)),IF(ISNA(VLOOKUP(B115,sat1,4,FALSE)),0,VLOOKUP(B115,sat1,4,FALSE)),IF(ISNA(VLOOKUP(B115,sun1,4,FALSE)),0,VLOOKUP(B115,sun1,4,FALSE)))

That wraps each individual VLOOKUP() in a test for #N/A and if the
individual VLOOKUP() fails, then a 0 is used as the value for just that one.

 

[T. Valko]

If you put the tables in the same columns so that they're one underneath
another then a simple SUMIF will do what you want.