|
SUMPRODUCT
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 |
SUMPRODUCT
Try this...
=SUMPRODUCT(($C$2:$C$20=B2)*($D$2:$D$200))-SUMPRODUCT(($C$2:$C$20=B2)*($D$2:$D$20<0)) If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "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 |
SUMPRODUCT
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 |
SUMPRODUCT
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$200))-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 "Ms-Exl-Learner" wrote: Try this... =SUMPRODUCT(($C$2:$C$20=B2)*($D$2:$D$200))-SUMPRODUCT(($C$2:$C$20=B2)*($D$2:$D$20<0)) If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "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 |
SUMPRODUCT
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 |
SUMPRODUCT
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 |
| All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com