#N/A Shortcut
 

Often I am using lookup functions which may or may not find an exact match
(which is what I want). From these results, typically in a column, I might
want to create a sum or, using two such columns, even a sumproduct result.

The usual way I deal with this is to write an if statement that says, if the
vlookup result is #N/A, then substitute zero. This yields long formulas
since the lookup function is already pretty long.

I'm wondering if there is a more elegant way to have the sumproduct compute
while ignoring all entries that are #N/A. Perhaps I could do this with a
sumif type function, but I don't know how to do this.

Help, please.

Dean

#N/A Shortcut
 

One way is to amend the if statement:

=IF(ISERROR(MATCH("Lookup Value","column of lookup
array",0)),0,Lookup(...............)

--
steveB

Remove "AYN" from email to respond
"Dean" wrote in message
...
Often I am using lookup functions which may or may not find an exact match
(which is what I want). From these results, typically in a column, I
might want to create a sum or, using two such columns, even a sumproduct
result.

The usual way I deal with this is to write an if statement that says, if
the vlookup result is #N/A, then substitute zero. This yields long
formulas since the lookup function is already pretty long.

I'm wondering if there is a more elegant way to have the sumproduct
compute while ignoring all entries that are #N/A. Perhaps I could do this
with a sumif type function, but I don't know how to do this.

Help, please.

Dean

#N/A Shortcut
 

=SUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100)

--
HTH

Bob Phillips

"Dean" wrote in message
...
Often I am using lookup functions which may or may not find an exact match
(which is what I want). From these results, typically in a column, I
might
want to create a sum or, using two such columns, even a sumproduct result.

The usual way I deal with this is to write an if statement that says, if
the
vlookup result is #N/A, then substitute zero. This yields long formulas
since the lookup function is already pretty long.

I'm wondering if there is a more elegant way to have the sumproduct
compute
while ignoring all entries that are #N/A. Perhaps I could do this with a
sumif type function, but I don't know how to do this.

Help, please.

Dean

#N/A Shortcut
 

Thanks for your help, but I'll need a little more. Inside your formula, you
have two hyphens and I don't know what that means.

Also, I thought I would need isna for a lookup function when it can't find a
match, not iserror (though I imagine it's analogous). Please clarify.

Assume that I have entries in cells a6, b6, and c6; also a7,b7, and c7. and
that the first three are 1, #n/a, and 2 and a7, b7, and c7 were 3,4, and 5.
Please write the exact formula for me. I'm thinking the result should be
1*3 + 2 *5 = 13.

Thanks again!
Dean


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100)

--
HTH

Bob Phillips

"Dean" wrote in message
...
Often I am using lookup functions which may or may not find an exact
match
(which is what I want). From these results, typically in a column, I
might
want to create a sum or, using two such columns, even a sumproduct
result.

The usual way I deal with this is to write an if statement that says, if
the
vlookup result is #N/A, then substitute zero. This yields long formulas
since the lookup function is already pretty long.

I'm wondering if there is a more elegant way to have the sumproduct
compute
while ignoring all entries that are #N/A. Perhaps I could do this with a
sumif type function, but I don't know how to do this.

Help, please.

Dean

#N/A Shortcut
 

If you creat cells (say A8, B8, C8)
with = A6*A7
= B6*B7
= C6*C7

than you can use a simiple =SumIf(A8:C8,"<#N/A)

--
steveB

Remove "AYN" from email to respond
"Dean" wrote in message
...
Thanks for your help, but I'll need a little more. Inside your formula,
you have two hyphens and I don't know what that means.

Also, I thought I would need isna for a lookup function when it can't find
a match, not iserror (though I imagine it's analogous). Please clarify.

Assume that I have entries in cells a6, b6, and c6; also a7,b7, and c7.
and that the first three are 1, #n/a, and 2 and a7, b7, and c7 were 3,4,
and 5. Please write the exact formula for me. I'm thinking the result
should be 1*3 + 2 *5 = 13.

Thanks again!
Dean


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100)

--
HTH

Bob Phillips

"Dean" wrote in message
...
Often I am using lookup functions which may or may not find an exact
match
(which is what I want). From these results, typically in a column, I
might
want to create a sum or, using two such columns, even a sumproduct
result.

The usual way I deal with this is to write an if statement that says, if
the
vlookup result is #N/A, then substitute zero. This yields long formulas
since the lookup function is already pretty long.

I'm wondering if there is a more elegant way to have the sumproduct
compute
while ignoring all entries that are #N/A. Perhaps I could do this with
a
sumif type function, but I don't know how to do this.

Help, please.

Dean

#N/A Shortcut
 

Forgot:

IsError says true if = #N/A

--
steveB

Remove "AYN" from email to respond
"Dean" wrote in message
...
Thanks for your help, but I'll need a little more. Inside your formula,
you have two hyphens and I don't know what that means.

Also, I thought I would need isna for a lookup function when it can't find
a match, not iserror (though I imagine it's analogous). Please clarify.

Assume that I have entries in cells a6, b6, and c6; also a7,b7, and c7.
and that the first three are 1, #n/a, and 2 and a7, b7, and c7 were 3,4,
and 5. Please write the exact formula for me. I'm thinking the result
should be 1*3 + 2 *5 = 13.

Thanks again!
Dean


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100)

--
HTH

Bob Phillips

"Dean" wrote in message
...
Often I am using lookup functions which may or may not find an exact
match
(which is what I want). From these results, typically in a column, I
might
want to create a sum or, using two such columns, even a sumproduct
result.

The usual way I deal with this is to write an if statement that says, if
the
vlookup result is #N/A, then substitute zero. This yields long formulas
since the lookup function is already pretty long.

I'm wondering if there is a more elegant way to have the sumproduct
compute
while ignoring all entries that are #N/A. Perhaps I could do this with
a
sumif type function, but I don't know how to do this.

Help, please.

Dean

#N/A Shortcut
 

"Dean" wrote in message
...
Thanks for your help, but I'll need a little more. Inside your formula,
you
have two hyphens and I don't know what that means.

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Also, I thought I would need isna for a lookup function when it can't find
a
match, not iserror (though I imagine it's analogous). Please clarify.

Assume that I have entries in cells a6, b6, and c6; also a7,b7, and c7.
and
that the first three are 1, #n/a, and 2 and a7, b7, and c7 were 3,4, and
5.
Please write the exact formula for me. I'm thinking the result should be
1*3 + 2 *5 = 13.

Best to use a different tack for this

=SUMIF(A6:C7,"<#N/A")