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

Hi Allison

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

I hope it help You

Jaan

"Allison" kirjutas:

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

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.
--
Thanks, Allison


"Jaan" wrote:

Hi Allison

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

I hope it help You

Jaan

"Allison" kirjutas:

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

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

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

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

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

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