I have a column to add that contains some #N/As. I'm using a dsum function
and need to be abke to add the column while ignaring the #N/As. The dsum
formula I'm using is:

=DSUM($A20:$AS1998,33,$A2002:$A2003)
--
Robert K

hi, Robert !

I have a column to add that contains some #N/As.
I'm using a dsum function and need to be abke to add the column while ignaring the #N/As.
The dsum formula I'm using is: =DSUM($A20:$AS1998,33,$A2002:$A2003)

"my" dsum(... function ignores error values (#name?, #value!, #div/0!, #n/a!, etc.)

could you be a little more specific on "what" you have/get/expect/... -?-
regards,
hector.

I don't understand you response. Let me describe my problem in more detail.
I have a table with multiple columns, column D has 1 of 3 entries, OE, AC&I
or Other. The column I am adding has either a number or #N/A (the entry
fcomes from another spredsheet which will show #N/A if not updated). In the
example I've given the database is from A20 to AS1998, 33 is the column I am
adding and A2002:A2003 is the heading Funding Source and under that OE. I
need to add the entire column 33 but get an answer of #N/A if even 1 entry is
#N/A. I have to add all the numbers in column 33 that have OE in column D
which is why I,m using the dsum function. How can I add column 33 and ignore
the #N/As so I get a numerical sum.

Thanks
--
Robert K


"RobertK" wrote:

I have a column to add that contains some #N/As. I'm using a dsum function
and need to be abke to add the column while ignaring the #N/As. The dsum
formula I'm using is:

=DSUM($A20:$AS1998,33,$A2002:$A2003)
--
Robert K

Also the #N/A in the cells is a return from a vlookup function. If that helps.
--
Robert K


"Héctor Miguel" wrote:

hi, Robert !

I have a column to add that contains some #N/As.
I'm using a dsum function and need to be abke to add the column while ignaring the #N/As.
The dsum formula I'm using is: =DSUM($A20:$AS1998,33,$A2002:$A2003)

"my" dsum(... function ignores error values (#name?, #value!, #div/0!, #n/a!, etc.)

could you be a little more specific on "what" you have/get/expect/... -?-
regards,
hector.

You should change the vlookup formulas to trap for the #N/A to start with.

=IF(ISNA(VLOOKUP(G1,$C$1:$F$31,2,FALSE)),"",VLOOKU P(G1,$C$1:$F$31,2,FALSE))

Then your DSUM should work properly.


Gord Dibben MS Excel MVP

On Sat, 3 May 2008 08:14:00 -0700, RobertK
wrote:

Also the #N/A in the cells is a return from a vlookup function. If that helps.

Yes, that works but by doing that I add 1.2mb to the size of my file. That
is why I'm trying to get rid of the #N/As in the dsum function of which I
have only 14 cells to contend with. Thanks.
--
Robert K


"Gord Dibben" wrote:

You should change the vlookup formulas to trap for the #N/A to start with.

=IF(ISNA(VLOOKUP(G1,$C$1:$F$31,2,FALSE)),"",VLOOKU P(G1,$C$1:$F$31,2,FALSE))

Then your DSUM should work properly.


Gord Dibben MS Excel MVP

On Sat, 3 May 2008 08:14:00 -0700, RobertK
wrote:

Also the #N/A in the cells is a return from a vlookup function. If that helps.

Maybe try changing the criteria range to A2002:B2003 with

B2002: heading for the numbers to sum (column AG)
B2003: <#N/A

Yes, that worked. Thank you very much.
--
Robert K


"Lori" wrote:

Maybe try changing the criteria range to A2002:B2003 with

B2002: heading for the numbers to sum (column AG)
B2003: <#N/A