Hi - requiring assistance, much appreciated!

[HelpandImproveAP]

Hi,

I have a column (N) with some sums of the totals of a few other columns (O, P, Q) that contains V Lookups. 

Some cells in column N add up to the respective sums in columns o, p, and q but other cells in column N amount to a #N/A figure (as not all cells in columns o, p, and q) return a value from other columns.

When I go to sort by largest number on column N the #N/A appears first, with the largest numbers underneath it... How can I fix this? 

Many thanks in advance.

A

[#2 · Accepted solution]

@HelpandImproveAP 

As is always true in Excel, there are many routes from Point A to Point B. How can you fix the situation so that #NA doesn't appear at the top of the sort?

One way, and only one way, would be to have whatever the formula is in column N wrapped in an IFERROR function, like this

=IFERROR(SUM(O2,P2,Q2),0)

which will cause a zero to appear instead of #NA. That's one way.

A better way (IMHO) would be to fix the formulas in columns O, P & Q so they're returning a value (possibly zero) rather than whatever it is that they're returning. In general, you want to fix a problem as close to the source as possible, rather than just putting a bandaid on a gaping sore,

It all has to do with what would be delivering a result with the highest integrity/reliability.

[#3]

You're amazing thank you!

I've come up with a workaround to wrap columns o,p,q. Appreciated!

[#4]

You're welcome.