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