Get SUM to ignore ### in a column

[JOGIER]

I am using a lookup function to return nalues to a column. Some of the values returned are ### and I cannot SUM this column. I either need to adjust the lookup formula to not return ### or adjust the SUM function to ignore ###. Any ideas?

Thanks!

Jim O

[Special-K]

At the moment we're all working in the dark except for you, post your file here.

[Shijesh Kumar]

u can adjust ur vlookup
something similar to below

Please Login or Register  to view this content.

[shg]

Presumably the error from a lookup is #N/A, not ###.

=SUM(IF(NOT(ISNA(A1:A10)), A1:A10))

Confirmed with Ctrl+Shift+Enter.

[daddylonglegs]

This formula will sum positive values and ignore errors

=SUMIF(A1:A10,">0")

or, if you have negative numbers too you can just SUM without #N/A

=SUMIF(A1:A10,"<>#N/A")

[JOGIER]

Thanks to all for the information. That takes care of the problem.

Jim O