Add Two Columns Together And Then Divide The Sum By Another Column
I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:
4+4/636 = 79.5
d2+f2 (divided by) h2 = 79.5
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
 VLOOKUP And SUM Formulas: Pick Up Another Value In Column 9 And Should Add Value In Column 7 And 9 And Return It
 Add Two Numbers And Divide The Result By Another Number
 Divide Two Columns But Show My Zero
 Divide A Sum By Number If Items In List
 Format Table & Add Sum To End Of 2 Columns
 Formula That I Can Add, Subtract, Divide, Average, Etc, Etc These Numbers
 Function/Formula To Divide 2 Different Ranges & Add Them
 Divide Uneven Data Into Columns
 Count Number Of Rows That Have Numbers And Divide Sum Of Cells
 Add SUM To End Of Each Column
 SUM Formulas: Add Together The Numbers In A Column
 Column 9 To Be The Sum Of Columns 3, 6, 7, And 8
 If Column A = 0 Then Adjacent Cell In Column B Divide By .3
 Divide Without Using Temp Column
 Sum The Totals On One Column, If Two Other Columns Are True
 Sum Column If Criteria In Other Columns Are Met
 Add Like Text Totals Of Columns At Bottom Of Column
 Looking Up A Sum Of Data In Several Columns For Specific Values Of Another Column
 Convert Multiple Rows To Columns And Add Column Headers
 Sum Column Based On Other Columns Year & Month
 Sum Of 3rd Column Values Based On Nested If On 2 Other Columns
 Sum Alternate Columns Base On Previous Column Entry
 Nested Loop To Divide Adjacent Row By Column
 Add Sum To End Of Range To Sum All Cells Above
 Column SUM Produces #N/A (Vlookup To Find Values That I Want To SUM)
 Check One Column, Get Value From A Second Column And Display Sum In 3rd Column
 Sum In A Macro (sum One Column After The Last Cell With A Value In It)
 Sum Formula: Sum Of The Numbers In Column
 SUM Then Add 10 If 20
 Add Sum Function After Macro
 Add / Sum Based On Dates
 Add To Total And Sum In Same Cell
 Add Sum To End Of Row
 Add Row In Sum Formula
 If (Column A) Within Date Range Then Add Number In Column C
 Function To Sum A Column's Entries Based On Another Column
 Show Sum Of Numbers In One Column IF Another Column Has A Specific Category
 Finding The Sum For Values In One Column That Are Connect To A Value In The First Column
 Wanting To Sum All Amounts In One Column, From Criteria In Another Column
 Sum Numbers In Column If Date In Other Column Matches
 SUM Count And ADD Expression With Multipliers
 Variable = A Sum Formula To Add The Cells
 Add The Sum Of Cells By Color With Different Values
 Add/Sum Each Digit In Long Number
 Add Sum To Existing Vba Module
 Add Like Data From 2 Columns
 Figure Out How To Add More Columns
 Add The Number In Columns Together
 Sum The Columns
 Formula To Add Column A If Column B = Cell C1?
Add Two Numbers And Divide The Result By Another Number
I have a simple formula that adds two numbers and divide the result by another number K1: SUM(E1,F1) / H1 K2: SUM(E2,F2) / H2 K3: SUM(E3,F3) / H3 K4: SUM(E4,F4) / H4 K5: SUM(E5,F5) / H5 etc, etc and then I average the results AVERAGE(K1:K5) etc, etc When H1, H2, H3, etc is 0, it generates a #DIV/0! (since you cannot divide by 0) I want a formula that I everytime a H1, H2, H3, etc is 0, I want to set it to a specific number H1, H2, H3, etc to 15.00 For example: IF (H1 = 0, 15.00, sum(E1,F1) / H1) If the number provided (H1) is 0, set it to 15.00, otherwise add the two numbers (E1, F1), and divide this result by the number provided (H1) But this is where the problem is: In every single cell that I have NOT yet provided information, it treats the BLANK cells as if it was 0, and has set every single cell to 15.00.
View Replies!
View Related
Divide A Sum By Number If Items In List
I basically have lots of lists of 12 cells. how ever some of those lists contain errors (e.g instead of having 12 numbers it will only have 10) so instead of doing =sum(a1:a12)/12 i want to divide by the amount of numbers in that area in this case 10 so i can get an average.
View Replies!
View Related
Format Table & Add Sum To End Of 2 Columns
I have a code to apply formatting to my selected cells (a border and background color) as well as sum in rows in columns C and D. I have data in A1:D1508. The first row is titles. Column A has item codes, B is item titles, C is 2007 sales and D is 2006 sales. All the items are grouped by categories. For example A1:D7 is category 1 with row 8 containing a total in column C and D, A9:D14 is category 2 with totals in row 8. Not all the categories have the same number of rows. This code is giving me a Runtime error '1004': Applicationdefined or objectdefined error. I have searched the forums and can't find what is wrong with my code. Sub CustomFormating() With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With...............................
View Replies!
View Related
Formula That I Can Add, Subtract, Divide, Average, Etc, Etc These Numbers
Imagine I have cells A1 to A5 with a sequence of "numbers" like this: A1 = 5055 A2 = 4567 A3 = 5690 A4 = 4980 A5 = 5155 The left hand side numbers represent a score a minus sign () to separate and the right hand side represent another score. I want a formula that I can add, subtract, divide, average, etc, etc these numbers. If I want to add the left hand scores, for example, cells A1 to A3, sum would be 151 (50 + 45 + 56).
View Replies!
View Related
Divide Uneven Data Into Columns
I would normally sort out using Text to Columns, but I can't in this case as the remaining elements can vary. I have a list of names with Forename and Surname and then the elements after that vary. Names are written in one cell under a single column like so: John Smith 13/04/2000 James Smith 123 123 1233 Jane Smith 07122 12345 Another Smith AB99 9CD All I want to do is end up with 3 columns of data, Forname  Surname  Other Is it possible to use a formula to make the breaks into each column at the first 2 spaces only, therefore the contents after the second space would just end up in one cell?
View Replies!
View Related
Count Number Of Rows That Have Numbers And Divide Sum Of Cells
I have a 52 (one for each week) page workbook. I am trying to average cell J9 for the entire 52 pages. However the information is not added to the cell until the end of the week so week 3052 all have 0s and should not be counted in the dividing number. Is there a way to have Excel count the number of cells that have a number (not 0) and divide the sum of the cells but that number. =(WK1!J9+.....WK52!J9) / (counted number of cells not = 0)
View Replies!
View Related
Add SUM To End Of Each Column
I want to write a totals procedure to insert a sum() at the bottom of each column in a usedrange. I thought I could do something like this: Dim c As Column For Each c In usedrange ' insert sum() at bottom Next c However, column is not a valid object. How do I do this?
View Replies!
View Related
Column 9 To Be The Sum Of Columns 3, 6, 7, And 8
I need column 6 to show 15% of column 5, then I need column 9 to be the sum of columns 3, 6, 7, and 8. I just can't figure out how to do this. What I'm trying to do is take a wholesale price, get 15% of it to figure for shipping, then add the wholesale price, shipping charge, a drop ship charge, then my Paypal charge together so I can see what I am paying to ship a specific item...then I can figure out what to charge.
View Replies!
View Related
If Column A = 0 Then Adjacent Cell In Column B Divide By .3
Im wondering if this can be done in excel.. 2 columns if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30% this is the way I was trying to do this... =IF((O2:O6722)=0,DIVIDE(L2:L6722).3) I have tried it several different ways and cannot get it to work  is it my formula or can this even be done because I can't reference just one cell?
View Replies!
View Related
Divide Without Using Temp Column
In A1 I have the value 60 I want it in 1:00:00 format What I do is, I divide that by A1/24*60 and the copy that and pastespecialvalue on A1 Is there any way to convert the number 60 into 1:00:00 format without having to do calculation in temporary columns?
View Replies!
View Related
Sum The Totals On One Column, If Two Other Columns Are True
(Excel 2003 on windows XP.) I need to SUM the cells in column J when column E has the word "yes" and the range N:R has a letter "w" in one of the columns. Only one of the N:R columns will have an entry on any one row and it may not necessarily be a "W". I have Googled, and being a beginner I don't comprehend the result! Some people were talking about using an add in, but I won't be allowed to add software to our PC's.
View Replies!
View Related
Add Like Text Totals Of Columns At Bottom Of Column
Note that the "Fruit" and "Fruit Totals" column letter position will always be different every time I run the macro, so I would like the macro to find these columns by name rather than by column letter. I'd like a macro that adds the total number of fruits in the "Fruit" column by looking doing 2 things... 1. Add the total number of "Fruits" in the "Fruit" column, find the bottommost row of data in the "Fruit" column, go down 2 rows, and create the text named "Fruits Total" 2. In the cell directly to the right of "Fruits Total" put the value of the total number of fruits in this cell (which will be in the "Fruit Totals" column) 3. Add the total number of "Apples" in the "Fruit" column, find the bottommost row of data in the "Fruit" column, go down 2 rows, and create the text named "Apples Total" 4. In the cell directly to the right of "Apples Total" put the value of the total number of apples in this cell (which will also be in the "Fruit Totals" column) 5. Repeat steps 3 & 4 for the other fruits Best to show you an example ...
View Replies!
View Related
Looking Up A Sum Of Data In Several Columns For Specific Values Of Another Column
=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))SUM($I:$I) This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score. I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players. I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players. I am also trying to create a graph of date on the x axis against score to date on the y axis  everytime I try, the x axis is always numbers instead of the date?
View Replies!
View Related
Convert Multiple Rows To Columns And Add Column Headers
I'm currently faced with a spreadsheet that has data formatted like this: A 1 RandomRowofData1 2 RandomRowofData2 3 RandomRowofData3 4 RandomRowofData4 5 RandomRowofData5 6 RandomRowofData6 7 RandomRowofData7 8 RandomRowofData8 9 RandomRowofData9 Every 9 rows, a new "set" of data repeats itself (wow, this is so hard to put into words).... I need to figure out a way to get the data in column "A", every 9 rows, to transpose itself into 9 separate columns.
View Replies!
View Related
Sum Column Based On Other Columns Year & Month
I have the following variables in these columns Column 1: Ship (1064, 1065, 1066 as the field contents) Column 12: Date (21Feb08 as format) Column 13: Weld Length (1000 as format) Column 15: Defect Length (1000 as format) What I need doing is the following is in a single cell per month add up what the total weld length is as well as the defect length as I have Jan 08, Feb 08 etc on another sheet where these values will be returned. There is a seperate sheet for each Ship so would like a formula that I could ammend 1064 to 1065 etc
View Replies!
View Related
Sum Of 3rd Column Values Based On Nested If On 2 Other Columns
I have three columns as follows: ItemsWeekEnding Count a14/7/2007 10 a34/7/2007 3 a14/14/2007 11 a14/7/2007 5 a44/7/2007 6 a14/21/20078 a44/14/20072 a34/21/20071 a24/7/2007 6 a14/28/20077 a24/21/20074 a94/28/20079 I want to Sum Count values (column 3) for matching WeekEnding (Column 2) and Items (Column 1), example: a1 4/7/2007 15 as a1 for this weekend is occuring twice. I need the formula to do it in excel.
View Replies!
View Related
Sum Alternate Columns Base On Previous Column Entry
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1  31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
View Replies!
View Related
Nested Loop To Divide Adjacent Row By Column
I am trying to loop through Column Q2:Q2000, and divide the adjacent Row R2:HS2 by the value in the corresponding Q column. ie. cells R2: HS2 should all be divided by Q2 cells R3:HS3 should all be divided by Q3 etc until R2000:HS2000 are divided by Q2000 I presume that i would require an initial loop to scroll through the column and a seperate loop to scroll through the row values, but i cannot get my head around how this is done.
View Replies!
View Related
Check One Column, Get Value From A Second Column And Display Sum In 3rd Column
I am tying to total all the sales for a given customer. This is what I have at present Customer Sales CustA 1000 CustB 500 CustA 1250 CustC 750 Elsewhere in the spreadsheet I have the following Customer Total Sales CustA CustB CustC As you can probably see I need to get the sum of each customers sales and display this under Total Sales. I thought about using VBA but am not really sure where to start. If anyone has any ideas how I could progress this I would be most grateful. I am using excel 2007
View Replies!
View Related
Sum Formula: Sum Of The Numbers In Column
I would like a formula for cells B1, B2, & B3 that will give me the sum of the numbers in Column B which are next to their corresponding plan in Column A. The attachment is a sample of a sheet that will be having approx 500 rows to work with. I have tried using the "Sumif" funtion, but I can't seem to get it to work correctly.
View Replies!
View Related
Add Sum Function After Macro
I have found this code to spred my data from master worksheet to clients worksheets! Is there a code to add the Sum function in the last row on each of the new worksheets in specific columns where is needed? Sub ExtractReps() Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("MAIN") Set rng = Range("A:AG")
View Replies!
View Related
Add / Sum Based On Dates
The object is to automate a weekly time sheet based on daily information. The "Daily Time" worksheet contains all 365 days of the year. The work week runs Monday through Sunday. Daily time is posted to the "Daily Time" sheet from handwritten time cards. The Weekly Time is then automatically calculated for each person and each category based on the dates in the "Pay Period From:" field and "To:" field. For example if 01/01/09 (Monday) is in the "Pay Period From:" field and 01/07/09 (Sunday) is in the "To:" field, then the "Weekly Time" sheet will total the hours posted between those two dates. In this manner there will be a continuous record of daily time in the "Daily Time" worksheet and the "Weekly Time" worksheet will reflect whatever dates are used in the two fields. I realize that because it is simply two dates, it is possible to show any combination of days. Basically what I need is to be able to add the hours between two dates and obviously the dates will change on a weekly basis. EXAMPLE IS ATTACHED
View Replies!
View Related
Add Sum To End Of Row
i need a macro code for this. example this is the Original Workbook, columns D and E are hidden A B C F 1 1 1 1 2 2 2 2 3 3 3 3 . . . . . . . . nth there's a command button here that when clicked, it will open a new workbook showing like below example this is the new workbook A B C D 1 1 1 1 3 3 3 3 . . . . . . . . nth there is no problem on that part, I already have the code for that. I only need the code on 1. how to compute the sum of column C then placed the total on end row of column C. 2. how to compute the sum of column D then placed the total on end row of column D. how to determine if it is the end of the row on Column C, if it is the last row then compute the total, and then do the same on D.
View Replies!
View Related
If (Column A) Within Date Range Then Add Number In Column C
I'm still confused regarding a complex formula  rather n00b. Using Office 2007 with XP. Can't figure out with help or previous questions. The following is what I would like this formula to accomplish. Picture included below. Take all the values of column of C and add them together if and only if the corresponding date value in column A is during the month of January (and February, March, and so forth). Event DateNameTotal Amount2/21/2009$4,5002/22/2009$04/3/2009$04/25/2009$9,0004/30/2009$8,3005/2/2009$9,0005/7/2009$5,5005/9/2009$9,0005/12/2009$3,000
View Replies!
View Related
Function To Sum A Column's Entries Based On Another Column
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View Replies!
View Related
Show Sum Of Numbers In One Column IF Another Column Has A Specific Category
I have a worksheet which basically tracks time. the time is reported in Column C. In that row in Column E, there is a validation list with about 6 different categories in it. On the side of this "table" I have a list of all the categories and I want a value to be next to it that reports the sum of time (C) for each category (E). So for the "Routing" category, I would want the value to be the sum of just data on the timesheet that have "routing" in Column E.
View Replies!
View Related
Finding The Sum For Values In One Column That Are Connect To A Value In The First Column
I have two columns. One column has UPCs  some of which are duplicates. The second column just has number values. I'm trying to add the sum of all of the numbers in column two which are attached to their respective UPC. For example, COL A///// Col B 11111111111///// 10 00000000000///// 15 11111111111///// 10 11111111111///// 4 00000000000///// 2 So, I need a third and fourth column to give me the total value for a single SKU(col A) of all the values in col B. In this example the Third column would contain the SKU, and the fourth column would contain the sum of all values in column B that are associated with the single SKU in column three. The third and fourth column would look like this: COL C///// COL D 11111111111///// 24 00000000000///// 17
View Replies!
View Related
Sum Numbers In Column If Date In Other Column Matches
I have a spreadsheet which will be completed by numerous users, with a worksheet reserved for each area. The spreadsheet is to record the number of days lost to training etc on a weekly basis. Each worksheet has 3 columns – column A DESCRIPTION, column B WEEK COMMENCING DATE and column C DAYS LOST. The table will be completed by the manager’s as the info becomes available to them. I will be collating the data on another worksheet and need a formula that will look in column B for all instances of 01/10/07 and then sum the corresponding cells in column C, then do the same for 08/10/07 and so on. I have attached an example of a page. I thought it may be VLookup or Sumif, but I don’t know how to go about it.
View Replies!
View Related
SUM Count And ADD Expression With Multipliers
I'm trying to make an expression to calculate some fields in Excel 2007. Trying to check fields up to a given number and give them a high multiplier, and add the rest of the numbers with a lower multiplier. I have multiple fields with numbers in hundreds. I want to multiply the sum of this fields (up to 500) with 4. But the expression must also start the count from the first field, and when im hitting 500 as a sum I want the rest to be added with 2 as multiplier (the reason for the prioritizing of the fields is that they also have their own values/multipliers, but I'll skip that to avoid to much confusion here). It's probably a lousy description. I'll give an example: Field 1__Field 2__Field 3____Sum 100____300_____500______2800 How in earths name did I get that Sum you might ask. ((100 + 300 + 100) * 4) + (400 * 2) = 2800 400 is the number left from the third field wich I only want to multiply with 2 instead of 4. Another example: Field 1__Field 2__Field 3____Sum 300____600_____500______2000 ((300 + 200 + 0) * 4) + (900 * 2) = 3800 900 is taken from the rest value of field 2 and 3. If there any way of doing this "sum check and multiply, and then add the rest with a different multiplier"?
View Replies!
View Related
Variable = A Sum Formula To Add The Cells
ActiveCell = "=(SUM(R3C5:R[0]C[3]))" In a macro I use that formula to add the cells of column E (or 5) from Row 3 down to the row of the active cell which is in column H (or 8), (the active cell would be H9 in this instance); and the result goes to the active cell as the formula =SUM ($E$3:E9). But I want the result to go directly to a numeric variable, not to a cell. Something equivalent to this: a=(SUM(R3C5:R[0]C[3])), which is not correct. What would the correct syntax be?
View Replies!
View Related
Add The Sum Of Cells By Color With Different Values
I have this fantasy footy comp I run and every week I enter player by player scores. To make it easier for me I need to a strange formula. Basically can you make a formulua to sum cell based on text colour? Every week the 3 players with the highest scores get votes. Eg highest gets 3 votes, second highest 2 votes and 3rd highest 1 vote. After each game I look thorough the players and change the colour of the 3 best to make them stand out. Green on black for highest, yellow on black for 2nd highest and red on black for third highest. Is there a way to at the end of each week to have a the cell (for example cell v43 in my sample) to update or add up all the 3pts 2pts and 1pts that player has accumulated throughout the season?? In my example I have just hand counted them and inputed them.
View Replies!
View Related
Add/Sum Each Digit In Long Number
I have a cell with a number which is a result of several previous sums. It's a number with 8 to 11 digits. For example: 878567663. I need to add each digit of the number, and then add again the result until I end up with a single digit. In the above example it'd be (8+7+8+5+6+7+6+6+3=56), then (5+6=11), then (1+1=2) and put the end result (2) in a cell My main doubt is that I don't know how to use a simple "do while" type structure in Excel to achieve the above. Questions: 1.) How do I create a formula to do that?. I have seen solutions in this board that let me add each digit but that's it... I need to "reduce" it until only one digit is left... 2.) What about if the lenght of the number is not always the same (fe. in one cell is 8, in another is 11, the next one is again 8...)?
View Replies!
View Related
Add Sum To Existing Vba Module
I have an existing module that queries a SQL database and populates a worksheet using VBA. I would like to add to this module to include a sum of columns, but as this sheet is always dynamic, i am not sure how to sum this appropriately. for example, I have column B, I would like to add the rows from a certain point in the worksheet, but this is always dynamic, is there a way to accomodate for this so that I am always summing the column in the correct place?
View Replies!
View Related
Add Like Data From 2 Columns
I have two columns side by side which contains similar data. I need to combine the two columns into on so I can filter to get a list of unique data from both. Sample attached. I need to put the items in columns "BM & BN" into one column.(any free column on the same sheet). Am I missing something simple? I have tried using Advanced Filter etc. exampleCombine. zip
View Replies!
View Related
Figure Out How To Add More Columns
I have this great code, but I can;t figure out how to add more columns to it. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Column = 2 Then If Target.Value = "Q" Then With Target .Value = "" End With Else With Target With Selection.Font .Name = "Wingdings 2" .Size = 22 End With .Value = "Q" End With End If Cancel = True End If End Sub
View Replies!
View Related
Formula To Add Column A If Column B = Cell C1?
a b c d 1 .2 yes yes .3 2 .5 no no .5 3 .1 yes maybe 0 So, as in the above example, I need to put a formula in D1 so that it will take the sum of column a if column b = c1. I hope this makes sense. And keep in mind I will be continually adding rows to this and will need it to cover the entire row, not just part of a row.
View Replies!
View Related
