Sumproduct or ?????
Glenn wrote:
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
AA - I want to be able to count if "AA" in Cust table (Column B) 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"
Thank you
John
With AA in C2 and your Amount Table in A1:B10 (headers in row 1):
=SUMPRODUCT(($A$2:$A$10=C2)*SIGN($B$2:$B$10))
Missed your column headings:
=SUMPRODUCT(($C$2:$C$10=B2)*SIGN($D$2:$D$10))
|