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
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