# Sumif Vlookup (probably an easy question)

#### SuperSean

##### New Member
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!

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Seti

##### Well-known Member
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

##### Well-known Member
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

##### Active Member
=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.

##### MrExcel MVP
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

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

#### SuperSean

##### New Member
Wow you people are quick.

Thanks a ton!

Replies
2
Views
526
Replies
21
Views
562
Replies
9
Views
597
Replies
0
Views
313
Replies
5
Views
322

1,181,309
Messages
5,929,217
Members
436,656
Latest member
ssims

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back