Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can SUM a column based on values of other columns?
I need help Suming up Amount column based on values of three different
columns. Let's say I have the following Detail Data: Month Institute Account Type Amount Memo 1/1/2008 Bank of America Checking 500 Deposit 1/1/2008 Bank of America Checking -200 Check #111 1/1/2008 Bank of America Saving 400 Deposit 1/1/2008 Bank of America Saving 150 Deposit 1/1/2008 CitiBank Checking 50 Deposit 1/1/2008 CitiBank Saving 100 Deposit How can I SUM the AMOUNT column based on the unique values in the Month, Institute, and Account Type columns? The Summary result I am hoping for is: Month Institute Account Type Balance 1/1/2008 Bank of America Checking 300 Saving 550 1/1/2008 CitiBank Checking 50 Saving 100 I have been trying to resolve this issue and I am stuck! Any help would be highly appreciated. Thanks in advance |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can SUM a column based on values of other columns?
if your using it for a check register you can download a free one from the
net and it will work with your excel, I used google to find one hope that helps "Labkhand" wrote: I need help Suming up Amount column based on values of three different columns. Let's say I have the following Detail Data: Month Institute Account Type Amount Memo 1/1/2008 Bank of America Checking 500 Deposit 1/1/2008 Bank of America Checking -200 Check #111 1/1/2008 Bank of America Saving 400 Deposit 1/1/2008 Bank of America Saving 150 Deposit 1/1/2008 CitiBank Checking 50 Deposit 1/1/2008 CitiBank Saving 100 Deposit How can I SUM the AMOUNT column based on the unique values in the Month, Institute, and Account Type columns? The Summary result I am hoping for is: Month Institute Account Type Balance 1/1/2008 Bank of America Checking 300 Saving 550 1/1/2008 CitiBank Checking 50 Saving 100 I have been trying to resolve this issue and I am stuck! Any help would be highly appreciated. Thanks in advance |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can SUM a column based on values of other columns?
Have a look at SUMPRODUCT
Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html If you have Excel 2007, look at SUMIFS in Help Come back with more questions if you need help with either best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Labkhand" wrote in message ... I need help Suming up Amount column based on values of three different columns. Let's say I have the following Detail Data: Month Institute Account Type Amount Memo 1/1/2008 Bank of America Checking 500 Deposit 1/1/2008 Bank of America Checking -200 Check #111 1/1/2008 Bank of America Saving 400 Deposit 1/1/2008 Bank of America Saving 150 Deposit 1/1/2008 CitiBank Checking 50 Deposit 1/1/2008 CitiBank Saving 100 Deposit How can I SUM the AMOUNT column based on the unique values in the Month, Institute, and Account Type columns? The Summary result I am hoping for is: Month Institute Account Type Balance 1/1/2008 Bank of America Checking 300 Saving 550 1/1/2008 CitiBank Checking 50 Saving 100 I have been trying to resolve this issue and I am stuck! Any help would be highly appreciated. Thanks in advance |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can SUM a column based on values of other columns?
In addition to what Bernard suggested, a Pivot Table will also provide
this information. --JP On Oct 14, 12:52*pm, Labkhand wrote: I need help Suming up Amount column based on values of three different columns. * Let's say I have the following Detail Data: Month * Institute * * * * * * * * * *Account Type * * * Amount *Memo 1/1/2008 * * * *Bank of America Checking * * * * * * * * * * * * *500 * Deposit 1/1/2008 * * * *Bank of America Checking * * * * * * * * * *-200 * * * *Check #111 1/1/2008 * * * *Bank of America Saving * * * * * * * * * 400 * *Deposit 1/1/2008 * * * *Bank of America Saving * * * * * * * * * 150 * *Deposit 1/1/2008 * * * *CitiBank * * * * * * * * * * * *Checking * * * * * * * * * * * * 50 * * Deposit 1/1/2008 * * * *CitiBank * * * * * * * * * * * *Saving * * * * * * * * * 100 * *Deposit How can I SUM the AMOUNT column based on the unique values in the Month, Institute, and Account Type columns? The Summary result I am hoping for is: Month * Institute * * * * * * * * *Account Type * * * Balance 1/1/2008 * * * *Bank of America * *Checking * * * * * 300 * * * * * * * * * * * * * *Saving * * * * * * 550 1/1/2008 * * * *CitiBank * * * * * * * * * Checking * * * * * 50 * * * * * * * * * * * * * *Saving * * * * * * 100 I have been trying to resolve this issue and I am stuck! *Any help would be highly appreciated. Thanks in advance |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can SUM a column based on values of other columns?
Hi,
Here is one way, suppose your data is in A1:E100, then enter the following titles in columns F1:I1, (not really necessary) Month, Institute, Account Type. In F2:H2 enter Bank of America, 1/1/2008, and Checking. Then in cell I2 enter the following formula: =SUMPRODUCT(--($A$2:$A$7=F2),--($B$2:$B$7=G2),--($C$2:$C$7=H2),$D$2:$D$7) In rows 3 and on enter the conditions (month, institute and type) and then copy the formula in I2 down as far as necessary. Important: this formula is not based on month but date, if you want to base it on month you will need to modify it: =SUMPRODUCT(--(MONTH($A$2:$A$7)=F3),--($B$2:$B$7=G3),--($C$2:$C$7=H3),$D$2:$D$7) In this case you would change the entry in F3 to 1, meaning January. -- Thanks, Shane Devenshire "Labkhand" wrote: I need help Suming up Amount column based on values of three different columns. Let's say I have the following Detail Data: Month Institute Account Type Amount Memo 1/1/2008 Bank of America Checking 500 Deposit 1/1/2008 Bank of America Checking -200 Check #111 1/1/2008 Bank of America Saving 400 Deposit 1/1/2008 Bank of America Saving 150 Deposit 1/1/2008 CitiBank Checking 50 Deposit 1/1/2008 CitiBank Saving 100 Deposit How can I SUM the AMOUNT column based on the unique values in the Month, Institute, and Account Type columns? The Summary result I am hoping for is: Month Institute Account Type Balance 1/1/2008 Bank of America Checking 300 Saving 550 1/1/2008 CitiBank Checking 50 Saving 100 I have been trying to resolve this issue and I am stuck! Any help would be highly appreciated. Thanks in advance |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can SUM a column based on values of other columns?
Thanks all for responding to my posting. I used Shane explained solution
and it worked for me. I am doing some customization of my own so that is why I can not use an existing template. Thanks again. "ShaneDevenshire" wrote: Hi, Here is one way, suppose your data is in A1:E100, then enter the following titles in columns F1:I1, (not really necessary) Month, Institute, Account Type. In F2:H2 enter Bank of America, 1/1/2008, and Checking. Then in cell I2 enter the following formula: =SUMPRODUCT(--($A$2:$A$7=F2),--($B$2:$B$7=G2),--($C$2:$C$7=H2),$D$2:$D$7) In rows 3 and on enter the conditions (month, institute and type) and then copy the formula in I2 down as far as necessary. Important: this formula is not based on month but date, if you want to base it on month you will need to modify it: =SUMPRODUCT(--(MONTH($A$2:$A$7)=F3),--($B$2:$B$7=G3),--($C$2:$C$7=H3),$D$2:$D$7) In this case you would change the entry in F3 to 1, meaning January. -- Thanks, Shane Devenshire "Labkhand" wrote: I need help Suming up Amount column based on values of three different columns. Let's say I have the following Detail Data: Month Institute Account Type Amount Memo 1/1/2008 Bank of America Checking 500 Deposit 1/1/2008 Bank of America Checking -200 Check #111 1/1/2008 Bank of America Saving 400 Deposit 1/1/2008 Bank of America Saving 150 Deposit 1/1/2008 CitiBank Checking 50 Deposit 1/1/2008 CitiBank Saving 100 Deposit How can I SUM the AMOUNT column based on the unique values in the Month, Institute, and Account Type columns? The Summary result I am hoping for is: Month Institute Account Type Balance 1/1/2008 Bank of America Checking 300 Saving 550 1/1/2008 CitiBank Checking 50 Saving 100 I have been trying to resolve this issue and I am stuck! Any help would be highly appreciated. Thanks in advance |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How can SUM a column based on values of other columns?
Thanks all for responding to my problem. I have used Shane's solution which
works for me. I could not use an existing template since I am doing some customization of my own. Thanks again all. "ShaneDevenshire" wrote: Hi, Here is one way, suppose your data is in A1:E100, then enter the following titles in columns F1:I1, (not really necessary) Month, Institute, Account Type. In F2:H2 enter Bank of America, 1/1/2008, and Checking. Then in cell I2 enter the following formula: =SUMPRODUCT(--($A$2:$A$7=F2),--($B$2:$B$7=G2),--($C$2:$C$7=H2),$D$2:$D$7) In rows 3 and on enter the conditions (month, institute and type) and then copy the formula in I2 down as far as necessary. Important: this formula is not based on month but date, if you want to base it on month you will need to modify it: =SUMPRODUCT(--(MONTH($A$2:$A$7)=F3),--($B$2:$B$7=G3),--($C$2:$C$7=H3),$D$2:$D$7) In this case you would change the entry in F3 to 1, meaning January. -- Thanks, Shane Devenshire "Labkhand" wrote: I need help Suming up Amount column based on values of three different columns. Let's say I have the following Detail Data: Month Institute Account Type Amount Memo 1/1/2008 Bank of America Checking 500 Deposit 1/1/2008 Bank of America Checking -200 Check #111 1/1/2008 Bank of America Saving 400 Deposit 1/1/2008 Bank of America Saving 150 Deposit 1/1/2008 CitiBank Checking 50 Deposit 1/1/2008 CitiBank Saving 100 Deposit How can I SUM the AMOUNT column based on the unique values in the Month, Institute, and Account Type columns? The Summary result I am hoping for is: Month Institute Account Type Balance 1/1/2008 Bank of America Checking 300 Saving 550 1/1/2008 CitiBank Checking 50 Saving 100 I have been trying to resolve this issue and I am stuck! Any help would be highly appreciated. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Add up a column based on values in other columns | Excel Worksheet Functions | |||
Count entries in one column based on values in another column | Excel Worksheet Functions | |||
looking up values in 1 column based on 2 other columns | Excel Worksheet Functions | |||
Tallying columns based on values of 2 different columns | Excel Worksheet Functions |