Excluding #N/A results from a SUMPRODUCT

[Iscariot]

Morning all!

One last problem in my worksheet for me to overcome, and then I think I'm done with this asisgnment.

Please Login or Register  to view this content.

This is a formula I'm using, suggested by the simply wonderful Donkey, in figuring out in a illness report how many times a ***/illness/age and other equaility information appears. Now, a persons details may appear 2 or three times if they've had more than one illness, so the formula above shows how many people there are, so it doesn't count the same person twice.

Works fine for the most part, except in the equality information. The information in the Master_Data is populated by a VLOOKUP and in some cases we don't have everyones equality data as they may not be on the main sheet that holds everyones details, so for example in some people it'll throw up the result '#N/A' as the details don't exist.

These #N/A's stop the above calculation from working, so what I'm after is some way to do exactly what it does above, but ignore any incidents of an #N/A result. Is this possible?

Thanks for your time and help, it is much appriciated!

=I=

[DonkeyOte]

Flattery gets you everywhere...

You should adapt the VLOOKUP underpinning the formula such that #N/A is not returned

A traditional approach would be:

=IF(ISNUMBER(MATCH(A1,othersheet!A:A,0)),VLOOKUP(A1,othersheet!A:B,2,0),"")

An alternative for returning a text value would be:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",VLOOKUP(A1,othersheet!A:B,2,0)))

[Iscariot]

You my friend, are not only a lifesaver, but a living legend. Works perfectly now, resolved the issue immediately.

My thanks as always, I couldn't of got through this placement without your help. Thank you dearly.

=I=