Small problem with SUM() SUMIF() 2

[Scott24x7]

All,
I have a sheet that is populating a field with information using VLOOKUP() from another sheet in the same workbook. In some cases however, there is no match, so I get #N/A returned, which is fine. But, the problem is I want to total the values in that column, and when I use SUM or SUMIF to do so, I get #N/A as the total. I have played around with IFERR IFERROR and ISTEXT to try to get it to ignore it, but with no success.
Any advise?

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[AnotherHiggins]

I don't like seeing the #N/A errors, so I use an IF formula to block them. Doing this will also allow you to sum the results column.

The most obvious way to use the IF is something like:
[tab]=If(Iserror(VLookup(...)),"",VLookup(...))

But VLookups take more overhead than, say, a countif function. So I'd use something like
[tab]=If(Countif(FirstColumn,LookupItem)=0,"",VLookup(...))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[macropod]

Hi Scott,

Try the following array formulae:
=SUM(IF(ISERROR(A1:A100),,A1:A100))
or
=SUM(IF(ISNA(A1:A100),,A1:A100))
Adjust your ranges to suit. The first version traps all errors, the second, just #N/A errors.

Cheers

[MS MVP - Word]

 

[Scott24x7]

Brilliant! First solution worked a charm. Thanks.
-S

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."