?
Solved

Excel formula for adding cells

Posted on 2011-10-18
13
Medium Priority
?
218 Views
Last Modified: 2012-05-12
I am trying to add 2 cells up & display the sum in one of the cells.

Example: Add number in A1 to number in B1 & then display the total in B1. I'm basically trying to create a way to enter a number for a particular week to the yearly total & display it the new amount in the yearly total column.

Is this possible?? I'm sure there would be some sort of variable involved, but not sure how to do it.
0
Comment
Question by:Blinkr
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 36990259
It's not possible.  You'll be creating a circular reference because you are trying to sum a column with another column into itself.  Why not just create another column
0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 36990263
BTW:

Do you have a sample so we can look at it?  maybe we can suggest another way
0
 
LVL 1

Author Comment

by:Blinkr
ID: 36990436
I would like the entries from the first column cell to be added to the number in the 2nd column cell & then display the total in the 2nd column cell.
Test.xls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:etech0
ID: 36990533
Try running the following macro:
------------------------------------------------------
Sub AddTotals()

For each cell in rows("B:B")
    cell.value = cell.value + cell.offset(0,-1).value
Next cell

End Sub
------------------------------------------------------

(to run a macro, go to the visual basic editor, insert a blank module, and copy and paste the text into the module. you can then set the macro to run by clicking a button of the quick access toolbar, or assigning a keyboard shortcut.)
0
 
LVL 10

Expert Comment

by:etech0
ID: 36990535
!Whoops! forgot one line. use THIS code:
------------------------------------------------------------------

Sub AddTotals()

For each cell in rows("B:B")
    if cell.value = "" then exit for
    cell.value = cell.value + cell.offset(0,-1).value
Next cell

End Sub
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36990648
You can do this in one step:

Select the values in column A, then hit COPY

Then, select B2

Then, hit Paste Special, and select the ADD operation.  Hit OK.  

The two columns are added, with the result now in Column B.

here's a pictoral on that:http://www.contextures.com/xlDataEntry04.html

Cheers,

Dave
0
 
LVL 1

Author Comment

by:Blinkr
ID: 36993344
Thanks etech0, I will try that. But there about 5 sets of cells that will need to be added together.

Thanks dlmille, but I'm setting this up for someone else to enter the data & it automatically updates the total cell.

Also if I add an additional column to do this function I will have to add about 5 because there are about 5 sets of cells that need to be added.

This may make it clearer:
I have a column for "Weekly Referrals", then a column for "Total Referrals".
Then a column for "Weekly Visitors", then a column for "Total Visitors".
Then a column for "Weekly Meetings", then a column for "Total Meetings".
Then a column for "Weekly Ref. Received", then a column for "Total Ref. Received".
Then a column for "Weekly Dollars", then a column for "Total Dollars".
The Total columns are annual.

Now this is the reason for not adding additional column as it would make the spreadsheet to large to print & harder to understand.

Thanks for your quick responses!
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36998694
Hi Blinkr,

take a look at the attach and try entering data in the weekly columns. The workbook should automatically add the weekly number to the Year totals.

I did this with a change event macro. This is code on a worksheet module, that will automatically be triggered whenever a value on a sheet gets entered/changed.
For this exampe i have limited the trigger to columns A, C, E, G, I and rows 2 to 6.
See code in the Sheet1 module (double click Sheet1 in the VBA editor):
If Not Intersect(Target, Range("A2:A6,C2:C6,E2:E6,G2:G6,I2:I6")) Is Nothing Then

Change this to the final layout of your worksheet.

Kind regards,
Davy

Test-1-.xls
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37010658
The problem with these approaches is that if the routine occurs more than once then the values would go berzerk and there would be no way to return. This could be most dangerous with the change event.

I strongly recommend that the results be recorded in a new column instead of the same column and the column B can be hidden.
0
 
LVL 1

Author Comment

by:Blinkr
ID: 37011666
ssagibh, I agree & I have been trying out a couple of things to do this. Can you point me to a link that gives a description of how to hide the calculating cells?? Also there is a really simple answer to this, but how do you display the value in one cell in another??

Thanks for your suggestion.
0
 
LVL 10

Expert Comment

by:etech0
ID: 37012711
to display the value of A1 in another cell, type
=A1
in that cell.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37013266
This is how I would have done it.
ssaqibh-515890.flv
0
 
LVL 1

Author Closing Comment

by:Blinkr
ID: 37015049
Thanks!! This looks exactly like I wanted it to work in the first place.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question