# Thread: SUM column totals when there's #N/A

1. Registered User
Join Date
Dec 2006
Posts
39

## Unanswered: SUM column totals when there's #N/A

Hey all,

Here's my delima. Seems like there should be an easy solution out there, but I've tried a couple of things and can't get this to work.

What I have is a LOOKUP cell range that is pulling counts of Call Status' into the area.

=LOOKUP(2,1/(('Call Status'!\$A\$1:\$A\$100="Last name, First Name")*('Call Status'!\$D\$1:\$D\$100="Left Message")),'Call Status'!\$G\$1:\$G\$100)

All I need to do is SUM the columns, but the #N/As (non-existent) row calculations are keeping this from happening.

What can I do?

TIA..

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

The way I see it, you have 3 main choices.

1. You can amend your conditional lookup formulas so that they do not return #N/A error values. This is going to create a pretty long formula so possibly not the best option.

2. You can keep your lookup formulas and then use a helper column. The helper column would contain formulas such as this:
Code:
`=IF(ISNUMBER(B2),B2,)`
where B2 contains the lookup formula. You would then sum this helper column.

3. You can create a sum formula which sums the lookup formula results and has the capacity to ignore error values. A formula which does this is:
Code:
`=SUMIF(B2:B10,"<=9.99999999999999E307")`
where B2:B10 contains your lookup formulas.

Hope that helps...

3. Registered User
Join Date
Dec 2006
Posts
39
Thanks for the suggestions, Colin_L.

In response: #1 - totally agree with ya on that one.
#2 - this seems like the easier way to work with the data, and will probably be the action I take.
#3 - Didn't work. It returned a value of zero even if there was number in the range.

Again - much thanks and appreciation!

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

Option 3 should work.

If it returns 0 then that means you have a problem with the underlying data in the lookup table. It means that the numbers in column G are numbers stored as text, so your LOOKUP() formula is also returning a string that represents a number. The SUMIF() formula is looking for number data types, not string data types, which means that they are ignored.

So if you go for option 3 then you will have to convert column G from text to numbers.