yes..you are absolutely right!
i have given "B2" i / o "-B2"..!

one more question..the formula gives the right answer only when
the each ref no in col A is having some unique value col B..!
when the same value is repeated in col B for the same ref no in col A
there seems to be some glitch!
for example if i extend the data to A2:B15 as under..
A........B
111.... 50
112.... 100
113.... -50
114.... -100
111.... -50
115.... 150
112.... -50
114.... 50
114.... -50
113.... 100
111.... 50
113.... -100
111.... -50
114.... -50

what i am getting is
A.......B........C (getting) C (expecting)
111... 50... 2.......................1
112... 100... 0......................0
113... -50... 0.......................0
114... -100... 0......................0
111... -50... 2.......................1
115... 150... 0......................0
112... -50... 0.......................0
114... 50... 2.......................1
114... -50... 1.......................1
113... 100... 1......................1
111... 50... 2.......................1
113... -100.. 1......................1
111... -50... 2.......................1
114... -50... 1.......................0



any suggestion for alteration
in the function?

-via135


On Nov 13, 10:53 pm, "Roger Govier"
wrote:
Hi

You cannot have entered the formula as I gave you.
I get the following results
111 50 1
112 100 0
113 -50 0
114 -100 0
111 -50 1
115 150 0
112 -50 0
114 50 1
114 -50 1
113 100 0

--
Regards

Roger Govier

"via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe...

hi Roger!

i am getting the following results:

111 50 1
112 100 1
113 -50 1
114 -100 1
111 -50 1
115 150 1
112 -50 1
114 50 2
114 -50 2
113 100 1

-via135

Roger Govier wrote:
Hi

One way
In cell C2 enter the following and copy down
=SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))
There will be 1's against the matching items and 0's against the rest.
DataFilterAutofilteruse dropdown on column C to select rows with 1

hi!

[quoted text clipped - 13 lines]

-via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200611/1