Sum Vlookup where there are blanks
 

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),VL OOKUP(B115,thur1,4,FALSE),VLOOKUP(B115,fri1,4,FALS E),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),VL OOKUP(B115,thur1,4,FALSE),VLOOKUP(B115,fri1,4,FALS E),VLOOKUP(B115,sat1,4,FALSE),VLOOKUP(B115,sun1,4, FALSE)))

Many thanks in advance.

Sum Vlookup where there are blanks
 

Try this formula instead:
=SUM(IF(ISNA(VLOOKUP(B115,mon1,4,FALSE)),0,VLOOKUP (B115,mon1,4,FALSE)),IF(ISNA(VLOOKUP(B115,tue1,4,F ALSE)),0,VLOOKUP(B115,tue1,4,FALSE)),IF(ISNA(VLOOK UP(B115,weds1,4,FALSE)),0,VLOOKUP(B115,weds1,4,FAL SE)),IF(ISNA(VLOOKUP(B115,thur1,4,FALSE)),0,VLOOKU P(B115,thur1,4,FALSE)),IF(ISNA(VLOOKUP(B115,fri1,4 ,FALSE)),0,VLOOKUP(B115,fri1,4,FALSE)),IF(ISNA(VLO OKUP(B115,sat1,4,FALSE)),0,VLOOKUP(B115,sat1,4,FAL SE)),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.

"KneeDown2Up" wrote:

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),VL OOKUP(B115,thur1,4,FALSE),VLOOKUP(B115,fri1,4,FALS E),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),VL OOKUP(B115,thur1,4,FALSE),VLOOKUP(B115,fri1,4,FALS E),VLOOKUP(B115,sat1,4,FALSE),VLOOKUP(B115,sun1,4, FALSE)))

Many thanks in advance.

Sum Vlookup where there are blanks
 

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.

--
Biff
Microsoft Excel MVP


"KneeDown2Up" wrote in message
...
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),VL OOKUP(B115,thur1,4,FALSE),VLOOKUP(B115,fri1,4,FALS E),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),VL OOKUP(B115,thur1,4,FALSE),VLOOKUP(B115,fri1,4,FALS E),VLOOKUP(B115,sat1,4,FALSE),VLOOKUP(B115,sun1,4, FALSE)))

Many thanks in advance.