Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP using FALSE returning blanks and #N/A | Excel Worksheet Functions | |||
Vlookup blanks = zeros | Excel Discussion (Misc queries) | |||
Excel 2002 : Why VLOOKUP formula does not show blanks or N/A ? | Excel Discussion (Misc queries) | |||
blanks instead of "0" in vlookup | Excel Discussion (Misc queries) | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |