Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balance Sheet Formula
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balance Sheet Formula
Look at help on the SumIf Function
=sumif(C2:C10, A1, E2:E10) -- HTH... Jim Thomlinson "Canon" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balance Sheet Formula
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 "Canon" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balance Sheet Formula
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 "Canon" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balance Sheet Formula
=SUMIF(BankBalance!E4:E149,1001,BankBalance!C4:C14 9)
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" wrote: 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 "Canon" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balance Sheet Formula
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 "Canon" wrote in message ... =SUMIF(BankBalance!E4:E149,1001,BankBalance!C4:C14 9) 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" wrote: 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 "Canon" wrote in message ... 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 __________ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
balance sheet | New Users to Excel | |||
balance sheet | New Users to Excel | |||
Balance sheet | Excel Discussion (Misc queries) | |||
Balance sheet Formula | Excel Discussion (Misc queries) | |||
A balance sheet | Excel Worksheet Functions |