vlookup with sum
 

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

vlookup with sum
 

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

vlookup with sum
 

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

vlookup with sum
 

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

vlookup with sum
 

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

vlookup with sum
 

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

vlookup with sum
 

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

vlookup with sum
 

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

vlookup with sum
 

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

vlookup with sum
 

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