Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP using FALSE returning blanks and #N/A dread Excel Worksheet Functions 1 September 16th 08 10:18 PM
Vlookup blanks = zeros Marilyn Excel Discussion (Misc queries) 6 March 18th 07 02:21 AM
Excel 2002 : Why VLOOKUP formula does not show blanks or N/A ? Mr. Low Excel Discussion (Misc queries) 3 February 5th 07 03:26 PM
blanks instead of "0" in vlookup Patty via OfficeKB.com Excel Discussion (Misc queries) 2 July 8th 05 10:47 PM
Paste Special Skip Blanks not skipping blanks, but overwriting... gsrosin Excel Discussion (Misc queries) 0 February 22nd 05 04:33 AM


All times are GMT +1. The time now is 05:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"