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

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

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

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

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

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

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