| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| sumproduct | Excel Worksheet Functions | |||
| sumproduct help | Excel Worksheet Functions | |||
Linear Mode
