Ignoring #N/A in a DSUM function

R

RobertK

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)
 
H

Héctor Miguel

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)
Click to expand...

"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.
 
R

RobertK

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
 
G

Gord Dibben

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)),"",VLOOKUP(G1,$C$1:$F$31,2,FALSE))

Then your DSUM should work properly.


Gord Dibben MS Excel MVP
 
R

RobertK

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.
 
L

Lori

Maybe try changing the criteria range to A2002:B2003 with

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