Sumif Vlookup (probably an easy question)

[SuperSean]

Hi

It's probably taking me longer to write this question that it will be for you to answer it )
Here's my problem, I'm making a schedule for my employees. I want to be able to type in the name of the possition (eg. CK1) and have the rest of that possition's info pop up. I made a list of all the possitions in the department, the times they work and how many hours that is.
To do this I used the formula:
=VLOOKUP(B7,Sheet2!$A$1:$C$40,3,FALSE)
It works, gives me all the info I need but leaves me with a #NA in the cells where nothing is being displayed.

<U>The problem:</U>
I need to sum the hours...using the =SUM(B9:H9)idea, I get a total of #NA because my vlookup returns with a #NA if nothing's entered in one of the days. Any way to have this sum up nice, or to have the cells where I have a VLOOKUP formula not display that ugly #NA?

Here's an example:
<pre>
Mon Tues Total
SMITH CK1 Cafe
Bob 5:30-2:00 7:00-3:30
8.00 8.00 16.00


Mon Tues Total
SMITH #33
Bob 7:00-3:30 #NA
8.00 #NA #NA
</pre>

Any help would be greatly appreciated.

Thanks!

 

[Seti]

You could use the following adjustment to your formula:

=if(isna(VLOOKUP(B7,Sheet2!$A$1:$C$40,3,FALSE)),0,VLOOKUP(B7,Sheet2!$A$1:$C$40,3,FALSE))

It is a lot longer, but if speed is not important and the size of your spreadsheet is not too big, it works fine.

Is it OK to put a zero where the #NA was?

Seti

 

[plettieri]

If you need a blanck cell where the zeros apperar modify Seti's formular by "" instead of zero....=if(isna(VLOOKUP(B7,Sheet2!$A$1:$C$40,3,FALSE)),"",VLOOKUP(B7,Sheet2!$A$1:$C$40,3,FALSE))


hope this helps
pll

 

[s-o-s]

Instead of
=VLOOKUP(B7,Sheet2!$A$1:$C$40,3,FALSE)

Use
=IF(ISNA(VLOOKUP(B7,Sheet2!$A$1:$C$40,3,FALSE)),0,VLOOKUP(B7,Sheet2!$A$1:$C$40,3,FALSE))

There are other ways of testing but this gives you something that you can use to test any function.

ie you can also test the ISNA function using a Match which will be fractionally quicker.

 

[Aladin Akyurek]

A couple of options...

(a) Keep #N/A's as returned by the VLOOKUP formula and use SUMIF instead of SUM...

=SUMIF(B9:H9,"<>#N/A")

(b) Use one of:

=IF(ISNUMBER(MATCH(B7,Sheet2!$A$1:$A$40,0)),VLOOKUP(B7,Sheet2!$A$1:$C$40,3,0),"")

=IF(ISNA(SETV(VLOOKUP(B7,Sheet2!$A$1:$C$40,3,0))),"",GETV())

The latter requires that you install the morefunc.xll add-in.

 

[SuperSean]

If possible I'd rather have a blank space in there instead of a 0.

 

[SuperSean]

Wow you people are quick.

Thanks a ton!