vlookup with sum

A

Allison

I have a workbook with a sheet for each week of the year showing # of days
absent that week for only employees that were absent that week. I have
created a master sheet with all employee names and am doing a vlookup by emp#
for each week on the master sheet. All of that is OK. But I am now trying
to do a simple sum on the total absent for the year and sum does not work
with the #N/A error. Can you help?

FIRST LAST EMP# DIVISION Week 1 Week 2 Week 3
Sara Kling GW29 Germany 1 1 1
Sean Willis GBW09 Great Britain #N/A 1 #N/A
Colleen Abel CW58 Canada 2 #N/A #N/A
Teri Binga AW55 Australia #N/A 2 #N/A
Frank Culbert GBC07 Great Britain 3 #N/A #N/A
Kristen DeVinney GBS45 Great Britain #N/A 3 #N/A
Theresa Califano CW19 Canada 0 #N/A #N/A
Barry Bally GC04 Germany #N/A 4 #N/A
Cheryl Halal CA26 Canada 1 #N/A #N/A
 
J

Jaan

Hi Allison

I'm not sure, is it sole right.
=SUMIF(your range;"<>#N/A")

I hope it help You

Jaan

"Allison" kirjutas:
 
A

Allison

Thanks so much for your help. I was on to the SUMIF, but am still confused
as to why this works. The contents of the cells that have #N/A is really a
formula (the vlookup), so do you get why in the sumif Excel sees the #N/A and
can use it?

Also, I am not sure what you mean by I'd be better off fixing the #N/A
errors. These really aren't errors. The #N/A is returned because that
employee number does not appear for that week (that person wasn't absent that
week).
Thanks again.
 
A

Allison

Thanks so much! It worked! I was on to the sumif, and have asked Biff on
this thread a question about it, as that person commented on my problem.
 
B

Biff

The #N/A is the value of the cell. The logic of the formula is:

Sum if E2:G2 is not equal to #N/A. said:
These really aren't errors.
Click to expand...

Technically, you're correct. #N/A means not available but most people
consider that to be an "error". Personally, I find all those #N/A's
unsightly and I'm sure many people would agree. You can write your lookup
formula so that if the info you're looking for isn't found instead of
returning #N/A you can return a blank cell. Like this:

=IF(ISNA(VLOOKUP(...............)),"",VLOOKUP(..............))

Then you could use a simple =SUM(E2:G2). Errors like #N/A usually just cause
problems so you're better off fixing them (if they might be expected).

Biff
 
A

Allison

Biff: thanks for following up. I will definitely try that formula that you
suggested because those N/As are ugly.
I still don't get why #N/A is the value of the cell. The contents is
definitely the vlookup formula, so I am still confused as to why this works.
I really appreciate your time and patience!
 
B

Biff

I still don't get why #N/A is the value of the cell.
The contents is definitely the vlookup formula,
so I am still confused as to why this works.
Click to expand...

A cell can have only 2 types of entries. A constant or a formula. A constant
is when you type something into a cell like the word "Yes" or the number 10.
A formula is typed into the cell but the value that gets entered into that
cell is CALCULATED by the formula. Excel knows you've entered a formula
because a formula starts with an = sign.

The CALCULATED result of your Vlookup formula is either a number, or, if the
lookup value is not found, the "error" #N/A. (Not Available).

..............A..........
1..........10
2.......#N/A
3..........10
4..........10

=SUMIF(A1:A4,"<>N/A")

SUM IF A1:A4 does not equal (<>) #N/A

A1 does not equal #N/A
A2 is equal to #N/A
A3 does not equal #N/A
A4 does not equal #N/A

So, the result is the SUM of A1, A3 and A4 because those cells do not equal
#N/A.

Hope that helps!

BTW, I love explaining how this "stuff" works!

Biff
 
A

Allison

Thanks again for taking the time to respond! That makes everything crystal
clear! So when a formula is entered in a cell, it is either looking for the
constant entry OR the calculated result of another formula, and #N/A is
actually the calculated result. It is so easy to make this assumption with a
number that is a calcualted result, I just wasn't putting an "error" message
into the same category as a numeric result. I guess I did know this when
text is returned on an =if statement, but again the "error" was confusing me.
Thanks again for spending all the time with me! I really appreciated you
help!
 
B

Biff

You're welcome. Thanks for the feedback!

Biff

Allison said:
Thanks again for taking the time to respond! That makes everything
crystal
clear! So when a formula is entered in a cell, it is either looking for
the
constant entry OR the calculated result of another formula, and #N/A is
actually the calculated result. It is so easy to make this assumption
with a
number that is a calcualted result, I just wasn't putting an "error"
message
into the same category as a numeric result. I guess I did know this when
text is returned on an =if statement, but again the "error" was confusing
me.
Thanks again for spending all the time with me! I really appreciated you
help!
Click to expand...
 
Top