You're welcome. Thanks for the feedback!

Biff

"Allison" wrote in message
...
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!
--
Thanks, Allison


"Biff" wrote:

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.

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

"Allison" wrote in message
...
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!
--
Thanks, Allison


"Biff" wrote:

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. < means not equal.

These really aren't errors.

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

"Allison" wrote in message
...
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.
--
Thanks, Allison


"Biff" wrote:

You'd be better off fixing the #N/A errors:

=SUMIF(E2:G2,"<#N/A")

Biff

"Allison" wrote in message
...
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

--
Thanks, Allison