Formulae when some cells contain #N/A
 

How do I sum cells that include #N/A in the range. The #N/A is the result of
a lookup formulae. I want such cells to register as 0. Currently the sum of
the cells gives #N/A as the answer.
Thanks

Heather

Use the ISNA function, thus

=IF(ISNA(ExistingVlookup),0,ExistingVlookup)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"HeatherC" wrote in message
...
How do I sum cells that include #N/A in the range. The #N/A is the result
of
a lookup formulae. I want such cells to register as 0. Currently the sum
of
the cells gives #N/A as the answer.
Thanks

Always try and fix errors at source rather than compensating for them.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"HeatherC" wrote in message
...
How do I sum cells that include #N/A in the range. The #N/A is the result
of
a lookup formulae. I want such cells to register as 0. Currently the sum
of
the cells gives #N/A as the answer.
Thanks

-----Original Message-----
Always try and fix errors at source rather than
compensating for them.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------
------------------
It's easier to beg forgiveness than ask
permission :-)
----------------------------------------------------------
------------------

"HeatherC" wrote in
message
...
How do I sum cells that include #N/A in the range. The
#N/A is the result
of
a lookup formulae. I want such cells to register as 0.
Currently the sum
of
the cells gives #N/A as the answer.
Thanks

You can try using IIf Function

or If Then constructs.

.

I am having the same problem!
I have several data tables of identical layout that are updated daily, the
first two manually and the others by preset formula that calculates from the
first two. If a zero or no data are input into cells in the first two then
the formula of the others will return #NA, this is intentional because charts
are plotted from the latter tables and #NA prevents zero's being plotted all
over it for data that has not been entered yet (zero is a valid in negative
and positive data entry only when entered).

Each table has a SUM total but if #NA exits in any of the cells totaled the
SUM is returned #NA.

"Nick Hodge" wrote:

Heather

Use the ISNA function, thus

=IF(ISNA(ExistingVlookup),0,ExistingVlookup)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"HeatherC" wrote in message
...
How do I sum cells that include #N/A in the range. The #N/A is the result
of
a lookup formulae. I want such cells to register as 0. Currently the sum
of
the cells gives #N/A as the answer.
Thanks

PS I tried the ISNA method you proposed (assuming that ExistingVlookup was
the range) but that resulted in #VALUE!

"Lee IT" wrote:

I am having the same problem!
I have several data tables of identical layout that are updated daily, the
first two manually and the others by preset formula that calculates from the
first two. If a zero or no data are input into cells in the first two then
the formula of the others will return #NA, this is intentional because charts
are plotted from the latter tables and #NA prevents zero's being plotted all
over it for data that has not been entered yet (zero is a valid in negative
and positive data entry only when entered).

Each table has a SUM total but if #NA exits in any of the cells totaled the
SUM is returned #NA.

"Nick Hodge" wrote:

Heather

Use the ISNA function, thus

=IF(ISNA(ExistingVlookup),0,ExistingVlookup)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"HeatherC" wrote in message
...
How do I sum cells that include #N/A in the range. The #N/A is the result
of
a lookup formulae. I want such cells to register as 0. Currently the sum
of
the cells gives #N/A as the answer.
Thanks

existingvlookup was a shortcut for not writing your formula.


It would look more like this in real life:

=if(isna(vlookup(a1,sheet2!a1:b99,2,false),0,vlook up(a1,sheet2!a1:b99,2,false)))


Lee IT wrote:

PS I tried the ISNA method you proposed (assuming that ExistingVlookup was
the range) but that resulted in #VALUE!

"Lee IT" wrote:

I am having the same problem!
I have several data tables of identical layout that are updated daily, the
first two manually and the others by preset formula that calculates from the
first two. If a zero or no data are input into cells in the first two then
the formula of the others will return #NA, this is intentional because charts
are plotted from the latter tables and #NA prevents zero's being plotted all
over it for data that has not been entered yet (zero is a valid in negative
and positive data entry only when entered).

Each table has a SUM total but if #NA exits in any of the cells totaled the
SUM is returned #NA.

"Nick Hodge" wrote:

Heather

Use the ISNA function, thus

=IF(ISNA(ExistingVlookup),0,ExistingVlookup)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"HeatherC" wrote in message
...
How do I sum cells that include #N/A in the range. The #N/A is the result
of
a lookup formulae. I want such cells to register as 0. Currently the sum
of
the cells gives #N/A as the answer.
Thanks




--

Dave Peterson