Thread: lOOKUP -ve value

Evening,

I'd appreciate any help on the following dilema please.

I'm in a worksheet named Accounts and I need to reference the cell in A2 and look up this reference in column A of a worksheet named Reports and return the value in column P of the owrksheet named Reports if the value is -ve.

I have two occurences of the account reference in worksheet named Reports and I want to return the -ve value to cell N36 of worksheet Accounts and the +ve value to cell N37.

Hope this makes some sense, any help appreciated.

Thanks

JH

if you only have 2 occurances of any account ref in one column and the corresponding values are one -ve and one +ve(or zero) you can do it like this
=SUMPRODUCT((reports!$a$1:$a$100=$A$2)*(reports!$p$1:$p1$100>=0),(reports!$p$1:$p1$100)) for +ve

=SUMPRODUCT((reports!$a$1:$a$100=$A$2)*(reports!$p$1:$p1$100<0),(reports!$p$1:$p1$100)) for -ve
then again if you had the same account say 4 times with 2 +ve and 2-ve it would give the total of +ve and the total of the -ve's