Can SUMPRODUCT be used when only two ranges are specified?

[psulion01]

I can't get the following formula to work... is it possible?

SUMPRODUCT((Range1<>Range2)*(DollarValue))

Basically I have a master sheet of data:

Range1 DollarValue
AAA 20
BBB 35
CCC 5
DDD 10

and a separate sheet with items i want excluded from the calc:

Range2
BBB
DDD

I want the SUMPRODUCT to give me a value of 25 (AAA + CCC, excluding BBB and DDD from the calc). However, I'm getting #N/A in my formula cell. Is there an easy way to do this other than specifying each value to exclude, e.g. SUMPRODUCT((Range1<>"BBB")*(Range1<>"DDD")*(DollarValue))

I have a lot of values to exclude, so this could get very hard to manage...

thanks,
Mike

 

[psulion01]

Cool...all seem to work... any reason to use one over the other?

 

[Aladin Akyurek]

Cool...all seem to work... any reason to use one over the other?

Primarily the calculation efficiency...

 

[psulion01]

figued that much... which is the most efficient (or should be)?

 

[Aladin Akyurek]

figued that much... which is the most efficient (or should be)?

I expect them to have about the same score of efficiency, although I'm a bit biased towards the one with SumIf...