Sumif Vlookup (probably an easy question)

SuperSean

New Member
Joined
Apr 30, 2002
Messages
45
Hi

It's probably taking me longer to write this question that it will be for you to answer it :eek:)
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
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
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
Joined
Apr 14, 2002
Messages
384
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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.
 

Forum statistics

Threads
1,144,329
Messages
5,723,737
Members
422,512
Latest member
MHau5

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?

Disable AdBlock

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
Top