Trouble with #N/A Using CSE formula to SUM

Posted by Jeremy Kaufman on January 30, 2002 9:09 AM

I am using the following formula. This sums all the "se" entries in L3:L143 correctly until it encounters #N/A. How do I sum all the "se" entries even if one "se" entry contains #N/A?

=SUM(IF(Sheet1!D$3:D$143="se",Sheet1!L$3:L$143))

Posted by Mark W. on January 30, 2002 9:20 AM

{=SUM(IF(D$3:D$143="se",IF(ISNA(L$3:L$143),0,L$3:L$143)))}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.