Thank you so much, Jacob. Actually, it was working.

"Jacob Skaria" wrote:

The posted formula will consider not just AA or BB for a matching but all
cells in the range B1:B20 for a match. Try changing that reference to B1:B2
with B1 entered with 'AA' and B2 entered with 'BB'...

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

Thanks, Jacob but this does not work.

"Jacob Skaria" wrote:

Try
=SUMPRODUCT((ISNUMBER(MATCH(C$2:C$20,B2:B20,0)))*( SIGN(D$2:D$20)))

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

Hi All,

I have 2 tables one is cust table and the other one is amount table.

A B C D
Customer Table Amount Table
Parent ID Cust # Cust # Amount
2001 AA AA 5
2001 BB AA 5
2001 CC AA 5
2001 DD AA 5
2001 EE AA -5
2001 FF AA -5
2001 GG BB 3
BB 3
BB -3

Summary Sheet

AA - I want to be able to count if (C2:C20) in Cust table (B2:B20) then count
positive amount minus negative amount in amount table (Column D). In this
case the answer is "2"

BB - The same thing with "BB". The answer is "1"

I change " B2 " to "B2:B20" and it does not work on this formula.
=SUMPRODUCT((C$2:C$20=B2)*(SIGN(D$2:D$20)))


Thank you
John