formula works but...

[judoist]

Hi

Thanks for your help...

I wanted to be able to choose a specific value in column C to decid
whether sum was positive or negative. In this case, -1,1 or 2 (but no
zero, which should still return a positive value).

The following solution appears to work...

=SUMPRODUCT((A1:A600>=4)*(A1:A600<=9)*A1:A600*B1:B600)-(SUMPRODUCT((A1:A600>=4)*(A1:A600<=9)*(C1:C600={-1,1,2})*A1:A600*B1:B600))*2

... but i have one more problem.

Column A not only contains digits, but also X's. If the X's ar
removed, the above formula works fine. Unfortunately, as soon as an
is introduced the result is #VALUE!.

X, by the way represents the value 1 so i'd like this to be taken int
account as follows...

A1= X, B1= 2, C1= -1... result is -2
A2= X, B2= 1, C2= 5 ... result is 1
A3= X, B3= 1, C3= 2 ... result is 1

Does this make sense

 

[judoist]

Please ignore this message.

Should have been posted as a reply to earlier thread.

Thank