Thanks:  0
Likes:  0

both the results for the =ISNUMBER(XX) formulas was TRUE.

I typed the formula into the wrong sheet - DOH!

Sorry
Simon

2. On 2002-04-30 03:47, MrStressed wrote:

=ISNUMBER(B3) - my result - TRUE

Cheers
Simon
What we have here a data type mismatch that also leads to #N/A's when unexpected.

Go to the front sheet.
Copy an empty, unused cell.
Select all of the lookup values in B from B11 on.
Activate the option Edit|Paste Special >Add.

Now test again:

=ISNUMBER(B11)

which should return TRUE.

When you're thru with this, I'll propose a layout for your front sheet to compute the desired sums/totals for each of your lookup values.

My error on the =ISNUMBER(XX) formula. Both results were TRUE.

Cheers
Simon

In C11 enter:

=SUMIF(D11:O11,"<>#N/A")

Double click on the fill handle (the little black square) in the lower right corner of C11.

In D11 enter and double click on its fill handle:

=VLOOKUP(\$B11,midsSALCOM!B\$3:\$V\$905,H\$2,0)

In E11 enter and double click on its fill handle:

VLOOKUP(\$B11,birmSALCOM!B\$3:\$V\$912,H\$2,0)

In F11 enter and double click on its fill handle:

=VLOOKUP(\$B11,avctSALCOM!B\$3:\$V\$900,H\$2,0)

Follow up the above logic until you have all of your 12 lookup tables.

The above is more efficient than trying to avoid #N/A's in a single mega-formula.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•