Balance Sheet Formula

[Canon]

Excel 2007
I have a sheet with different values in column c, and different numbers in
column e. On a separate sheet, (cell A1) I would like to add up the values
that match the numbers.
ie. if any cell in column e = 2001, then add all the cells in column c that
are in the same row as the 2001 in column e.
i.e. colunm c column e
$25.00 2001
$75.00 2004
$50.00 2001
$45.00 2005
The total for the other sheet, (cell A1) should be $75.00, I would have to
repeat this formula for all the different numbers in column e.

 

[Jim Thomlinson]

Look at help on the SumIf Function

=sumif(C2:C10, A1, E2:E10)

 

[Eduardo]

Hi,
I assume your values are in sheet 1 and the summary in sheet 2
in column A sheet 2 you have the years and in column B you want the total
amount so enter

=sumproduct(--(A2=sheet1!$E$1:$E$10000),sheet1!$C$1:$C$10000)

change range to fit your needs, remember range has to be the same in both
sides of the formula

 

[Roger Govier]

Hi

The general solution to your request is
=SUMIF(Sheet1!E:E,2001,Sheet1!C:C)

Rather than using 2001, on sheet2 place 2001 in say B1, 2002 in C1 etc then
in B2 of Sheet2 enter
=SUMIF(Sheet1!E:E,B1,Sheet1!C:C)
and copy across as required
--
Regards
Roger Govier

Excel 2007
I have a sheet with different values in column c, and different numbers in
column e. On a separate sheet, (cell A1) I would like to add up the
values
that match the numbers.
ie. if any cell in column e = 2001, then add all the cells in column c
that
are in the same row as the 2001 in column e.
i.e. colunm c column e
$25.00 2001
$75.00 2004
$50.00 2001
$45.00 2005
The total for the other sheet, (cell A1) should be $75.00, I would have to
repeat this formula for all the different numbers in column e.

__________ Information from ESET Smart Security, version of virus
signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Canon]

=SUMIF(BankBalance!E4:E149,1001,BankBalance!C4:C149)
This formula is working fine, but I would like to go one step further.
Instead of having the 1001 where it is, I would like this part of the formula
to equal the value in a different cell, say D3 - so that if I ever change D3,
the rest of the formula will continue to work.

 

[Roger Govier]

Hi

That is what I said in the second part of my posting.
Place the value to be referenced in a cell so in the example you have just
quoted
=SUMIF(BankBalance!E4:E149,D3,BankBalance!C4:C149)
provided that the D3 cell is on the sheet where the formula exists

--
Regards
Roger Govier

=SUMIF(BankBalance!E4:E149,1001,BankBalance!C4:C149)
This formula is working fine, but I would like to go one step further.
Instead of having the 1001 where it is, I would like this part of the
formula
to equal the value in a different cell, say D3 - so that if I ever change
D3,
the rest of the formula will continue to work.



__________ Information from ESET Smart Security, version of virus
signature database 4752 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4752 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com