|
Sumproduct or ?????
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 |
Sumproduct or ?????
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)) |
Sumproduct or ?????
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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 |
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)) |
Sumproduct or ?????
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". "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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 |
Sumproduct or ?????
That formula works fine. It does exactly what you asked it to do.
For any row where the content of column B equals the content of column C in the same row, the sign of the content of row D is added to the total. If that wasn't what you wanted to do, you needed a different formula. You asked a different question originally, and Luke and Jacob answered that question, but it isn't clear what you now want to do. -- David Biddulph "John" wrote in message ... 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". "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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 |
| All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com