Sumproduct or ?????

  • Thread starter Thread starter John
  • Start date Start date
J

John

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
 
John said:
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
Click to expand...

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))
 
Didn't Jacob and I already answer a similar question?

In E2:
=SUMPRODUCT((C$2:C$20=B2)*(SIGN(D$2:D$20)))

Copy down as desired.
 
Glenn said:
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))
Click to expand...


Missed your column headings:

=SUMPRODUCT(($C$2:$C$10=B2)*SIGN($D$2:$D$10))
 
Hi Luke,

You guys did but this one is a little different. I want to be able to write
like this:
=SUMPRODUCT(($C$2:$C$20=$B$2:$B$20)*(SIGN(D$2:D$20)))

this one does not work because I change "B2" to "B2:B20".
 
Back
Top