Using Sums In Macros
Jul 26, 2006
I made a budget for my project and have to include a macro. I wanted to have the macro pop up a box for the user to input the expense they wanted summed for the year (student loan, car payment, etc) and then have the macro sum the payments from each month. I couldn't figure out how to do the pop up box either, so figured I could just do multiple macros (20 or so) but then ran into the added difficulty of needing to only use one cell out of every four.
I have columns for budgeted, actual, monthly varience and year-to- date varience and only wanted to sum the actual.) I have read the thread about summing every nth, but trying it without the macro didn't give me the same answer as adding each on a calculator. Also, trying to record a macro of just adding cells didn't work (I figured it probably wouldn't, but I tried anyways.)
View 9 Replies
ADVERTISEMENT
Feb 21, 2009
Deciding to try and get to grips with Excel for basic accounting, I'd just like to check some things before I start filling columns... Say in column D I have a list of names, and in column E I have a list of figures: John Smith £250 Harry Davis £350 John Smith £500 What would be the formula for finding all occurrences of John Smith, and adding up John Smith's figures to give a total? In the simple case above, the answer would be £750. Would it matter if there are any empty/blank rows in the list?
View 9 Replies
View Related
Feb 21, 2008
I have a list of tenants.
Column A is the building number
Column B is the Area in metres squared
Column C is the tenant name
What I want to do is sum the vacancy (in sqm) for each building.
Ie, look at column A and choose the rows relating to a particular building, whose number is in column D1
Then look just at those rows and select those tenants named as "vacant"
Then add the areas from column B of those vacant plots.
View 12 Replies
View Related
Feb 17, 2010
In basic terms I have column A containing a list of dates, starting from 01/01/2005 and increasing by 1 day for each row, In column B I have the value for the day. These dates and values are still being used so the number of rows will increase day by day. I would like a formula to tell me the average total for January. So it would need to SUM each January before giving the average. I realise I could prob do this with a pivot table but if someone could give me a hint for a formula that would be great.
View 2 Replies
View Related
Dec 10, 2013
I have a number in a cell, lets say its 900. I want to multiply this number by 12 and then divide that by 52.
So a calculator I would simply type in 900 x 12 / 52 = 207.7
On excel I tried:
=sum(a1*12) /12
and it didn't work....
View 2 Replies
View Related
Feb 12, 2009
I am trying to create a macro that will sum the total number of 1's 2's 3's 4's 5's '6s in a range of cells d17:100 and return the number of 1s to cell a3 and number of two's to cell a4 and number of 3s to cell a5 and so forth.
I also need this to run each time any changes to any cell on that particular worksheet is made - sort of like 'refresh all the sums' type of thing.
I have been working on this spreadsheet for weeks and can't get past this part!
View 9 Replies
View Related
Feb 25, 2009
I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.
View 9 Replies
View Related
Feb 2, 2009
I have a listing of numbers in different catagories such as the following:
Type A:
1
Type B:
2
Type C:
4
Total:
7
I want to be able to have a cell automatically calculate to say:
Type A:
"(1/7)"
Type B:
"(2/7)"
Type C:
"(4/7)"
with that exact formating.
View 8 Replies
View Related
Feb 12, 2013
create a macro that would insert three rows with the first row summing the values in the lower two rows in columns E to J. The third row will get a cell formatting of black fill. In addition I would like the three rows grouped with the sum row showing when the group is closed. When the macro is executed the rows will get inserted below the last row in the sheet that has a cell format fill of the color black.
There is data in rows below the last black filled row so the inserted rows will be between that data and the exiting black filled row.
Perhaps it would be useful to explain how the designer has set up this worksheet and how the users are utilizing it. After a group of rows is created or new groups added, to insert an additional row within an existing group the user selects the black filled row at the bottom of the group and inserts a new row. This would ensure the subtotal at the top row of the group captures the values in the new row because the sum formula includes the black filled row. The black filled row also serves as a separator for each group. How to improve the design and function but the creator and users are not so open.
View 1 Replies
View Related
Sep 4, 2008
Is there a formula or a function that adds up all of the sums in a row.
******** ******************** ************************************************************************>Microsoft Excel - Book4___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA14=ABCD14603 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Feb 5, 2008
I have a spreadsheet with 14 columns assigned to different regions. I need to remove all of the rows that have the contents of the rows under these columns as £0.00. In psydocode, the requirement is:
If Columns C:O = £0.00
Then delete
It's important that it only deletes the row if all the entries through C to O are £0.00.
View 7 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Jan 14, 2014
I have a ('start:end') function that's not working. A simplified example is shown below in the screen shots.
I want a given cell in sheet1 to produce the sum of factors within a given column/row arrangements and equations from sheet3 and sheet4.
I've tried all the arguments I can think of in their proper places including the ' arguments before and after start:end but nothing seems to work. My resulting answer is usually #REF!
sheet1.png
sheet3.png
sheet4.png
worksheets.png
View 14 Replies
View Related
Feb 2, 2009
I've been struggling to figure out why my Conditional Sum formulas using multiple criteria keep reporting zero. I am tracking resource loads for a project which is divided into 4 parts (Iterations). I am trying to sum the total resource allocations per Iteration. see attachment!
View 9 Replies
View Related
Oct 30, 2009
I have one cell that brings up a random number using RANDBETWEEN and I want to keep a running total in a separate cell, but I can't figure out a formula or function to do this.
View 6 Replies
View Related
Nov 28, 2013
i have a tab on the bottom that is labeled "Man hours 2013" i need to pull the information from this sheet and create a new tab that will pull the property name, how many weeks of service, and the total man hours for each property. i will then be adding a column were i enter the monthly price for this contract divided by the number of weeks we serviced to figure out our percentage of gain/loss.
View 11 Replies
View Related
Jan 16, 2014
If I enter 1 in a cell, 1.1 below, select both and drag down, I should get a vector: 1, 1.1, 1.2 etc.
However, in the most recent instance of seeing this problem, at 6.5 I get 6.50000000000001! 6.6 onwards is then correct, at 7.2 the value is again incorrect in the 14th decimal place, and the errors continue intermittently through the sequence.
Is there a fix for this? It's extremely time consuming to have to check each auto-incremented array like this.
View 4 Replies
View Related
Oct 27, 2009
..............
View 12 Replies
View Related
Dec 28, 2005
I really don't know how easy or difficult it might be, but here it is:
I have something like this (example) :
--------------------------------------
| A | B | C | D |
--------------------------------------
1 | 15| W1 | | |
2 | 5| W2 | | |
3 | 7| W1 | | |
4 | 9| W3 | | |
5 | 21| W4 | | |
6 | 14| W2 | | |
--------------------------------------
I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.)
View 9 Replies
View Related
Dec 19, 2008
Need to write a formula which sums the values in a range only to the date we mentioned. For ease sample date & requirements are highlighted in attached spreadsheet.
View 3 Replies
View Related
Nov 6, 2013
I have a pivot table/chart that I need to show the sums as top 20 (or any other number) and All others. How can I do this.
I have an example file here: [URL] ...........
View 4 Replies
View Related
Jun 26, 2014
My list has dates. I am using this formula to determine the quarter for the date: ="Q"&INT((MONTH(S2)/4)+1).
As well I am using a formula to determine the month for the date: =E2-DAY(E2)+1.
In my book I have some sheets showing totals by month, in other I total up by quarter. I am experiencing a variance between the quarterly total by months and the quarterly total by quarter.
So I thought I'd try this formula to reconcile the two--but no success: =SUMPRODUCT((System=$B5)*(Created>=Lists!M$2)*(Created
View 5 Replies
View Related
Oct 25, 2009
I have a large amount of data that I need to be able to summarize certain fields in order to be able to work with the data.
What I have is multiple lines which all pertain to one record and I need to be able to summarize these into one line - which would give me a sum in certain columns. I am working with thousands of lines of data.
There is about 10 columns in the worksheet. I want to be able to tell Excel that if certain columns all match, then to summarize these lines and give me a sum total on a couple of other columns. The data I am working with is invoice data, so I want to know if certain columns - ie - Vendor name, invoice number, cheque number are the same in each row, then to sum these records and provide a total for the invoice amount and cheque amount columns.
This sounds like a complex function to me but I am hoping that someone has a solution for me.
View 10 Replies
View Related
Jun 21, 2006
I am building an inventory simulation and have run into a problem. What i want is, when i change a number in cell H4, i want excel to find the sum of C25 and the cells "H4" up. If H4 is 5 then i need the sum of C20:C25...if H4 is 10 i need C15:C25. Does anyone have any thoughts on how to do this? I have attached a sample sheet to make it more clear.
View 2 Replies
View Related
Sep 2, 2007
I have a worksheet of hockey player statistics that I need to tally the Career Totals and the Totals for One Specific Team. Since each player has played a different amount of years in both their career and by team I cannot use a simple Sum formula as each player will have a different amount of rows to calculate and the rows may not be congruous.
The statistics also use text "--" to specify when a player did not play and "0" for when they did play but no statistics were registered. When the totals are calculated, I need to know whether they were registered or simply didn't play (i.e retaining the "--" in the totals instead of "0" if they did not play in those specific years). One example is listed, while the rest need filling in... attached is a partial sample of the data
View 7 Replies
View Related
Jan 23, 2008
Is there a way to append this code to accomidate accross all sheets?
Sub HideRows()
ActiveSheet.Unprotect Password:="admin"
On Error Resume Next
With Range("e4:e34")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction. Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
ActiveSheet.Protect Password:="admin"
End With
End Sub
View 9 Replies
View Related
May 29, 2009
I have raw data with date & days of the month .
From this raw data I want to get the collective sums for each day of the week using Pivot or any other means - I tried Pivot but propabaly due to lack of my understanding of the same the desired result was not recived
View 3 Replies
View Related
Sep 17, 2009
i have a "budget" worksheet and am trying to total different colored cells and want to know if this can be done. attach a sample.
View 3 Replies
View Related
Jan 14, 2009
I am trying to write a function that sums the values in a range in excess of 2. For example, if the cell is 3 add 1, if it is 5 add 3 etc.
Here is what i have so far, it compiles with no bugs, but it does not work in my worksheet. I wrote it in Sheet 1 (VBA) of the worksheet i am using it in.
View 2 Replies
View Related
Nov 20, 2008
In the attached workbook I have 3 sheets. The first 2 with the names starting with WO are individual sheets based on a single scaffold rental. The "Standing Equipment" worksheet is adding the components from the other two worksheets. Easy enough, but I am looking for either a formula or some VBA guidance on how to have the "Standing Equipment" worksheet automatically change it's formulas to incorporate new worksheets as they are either added or deleted, thus avoiding constant formula editing as scaffolds are erected or dismantled.
View 3 Replies
View Related
