Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 7th 10, 06:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 55
Default 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   Report Post  
Old January 7th 10, 06:24 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,942
Default 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   Report Post  
Old January 7th 10, 06:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,276
Default 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   Report Post  
Old January 7th 10, 06:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default 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   Report Post  
Old January 7th 10, 10:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 55
Default 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   Report Post  
Old January 7th 10, 11:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default 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
news
=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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
balance sheet sw New Users to Excel 5 January 26th 09 01:09 AM
balance sheet Ryan New Users to Excel 1 October 12th 06 08:31 PM
Balance sheet plowleg Excel Discussion (Misc queries) 6 November 7th 05 01:38 PM
Balance sheet Formula TC Excel Discussion (Misc queries) 0 February 23rd 05 08:13 PM
A balance sheet Ida Lundberg Excel Worksheet Functions 3 January 28th 05 11:52 PM


All times are GMT +1. The time now is 12:43 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017