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

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:
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:
where B2:B10 contains your lookup formulas.

Hope that helps...

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!

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.


Good luck with your project...
Colin