SUMPRODUCT

[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 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$20)))


Thank you
John

 

[Ms-Exl-Learner]

Try this...

=SUMPRODUCT(($C$2:$C$20=B2)*($D$2:$D$20>0))-SUMPRODUCT(($C$2:$C$20=B2)*($D$2:$D$20<0))

If this post helps, Click Yes!

 

[John]

Thanks, Jacob but this does not work.

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

If this post helps click Yes

 

[John]

Hi Ms-Exl-Learner,

Thank you for the reply however I do not want "B2". I want "B2:B20" so it
should be like this

=SUMPRODUCT(($C$2:$C$20=B$2:B$20)*($D$2:$D$20>0))-SUMPRODUCT(($C$2:$C$20=B$2:B$20)*($D$2:$D$20<0))

But this one does not work if I just change "B2" to "B2:B20".

Thank you
John

 

[Jacob Skaria]

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

 

[John]

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