List values from range, that aren't in range

[Kenny J]

I'm wondering if there is anyway to show a list in excel where the values in a list are not in a range of values. For example:

named range: Objects
1
3
4
5

sheet 2:
1 4
2 7

Sheet 3:
Something like:
=isumproduct((Sheet2!A1:B2<>Objects)*(Objects))
should equal 3

Sure I'm missing something.

[rylo]

Hi

Try this array entered (ctrl, shift, enter) formula

=SUM((ISNUMBER(MATCH(Objects,Sheet2!A1:A2,0))=FALSE)*1)
or
=SUM(ISNA(MATCH(Objects,Sheet2!A1:A2,0))*1)

rylo