Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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

In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.

View Replies!   View Related
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


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 Two Columns But Show My Zero
I have to divide two columns. Sometimes there is a zero in one of the columns. Now, I realize that nothing can be divided by zero, but isn't there a way to show a zero instead of the #ref symbol???

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 Run-time error '1004': Application-defined or object-defined 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 = 50-55
A2 = 45-67
A3 = 56-90
A4 = 49-80
A5 = 51-55

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
Function/Formula To Divide 2 Different Ranges & Add Them
I am familiar with the SUMPRODUCT function, but is there a similar function that will divide the cells of two columns and then add them together?

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 30-52 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
SUM Formulas: Add Together The Numbers In A Column
I'd like to use a SUM formula to add together the numbers in a column, however i would like to do this missing some rows out........ ie; A1:A11 plus A13:A20 plus A22:A30.

I would also like to do this with another column containg fractions.

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...


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 paste-special-value 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
Sum Column If Criteria In Other Columns Are Met
I am trying to create a formula that will sum up a value in one column if certain critieria residing in other columns are met. I have attached a sample and the criteria.

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 bottom-most 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 bottom-most 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:
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 (21-Feb-08 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
a24/7/2007 6

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
Add Sum To End Of Range To Sum All Cells Above
I'm getting a Type Mismatch (error 13) with the following:

Range("I1").End(xlDown).Offset(1, 0) = "= Sum(" & Range("I2", Range("I2").End(xlDown)) & ")"

Basically I want to do an autosum on the first blank cell for a list of numbers in a column

View Replies!   View Related
Column SUM Produces #N/A (Vlookup To Find Values That I Want To SUM)
I have a column using Vlookup to find values that I want to SUM.

Some of the look up values produce a #N/A and result in a total sum of #N/A.

How do I get the SUM of a column of numbers when all of the cell values are not in fact numbers.

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

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 In A Macro (sum One Column After The Last Cell With A Value In It)
I have a Macro and I would like sum one column after the last cell with a value in it. This cell has variable length with each file.

Is ther a way to include this function in my existing Macro?

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
SUM Then Add 10 If 20
I'd like to get the sum of a group of cells, then add 10 to that sum only if it equals 20. Can someone outline the formula I need to input in my cell?

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 To Total And Sum In Same Cell
I would like to do the following:

The current value in A1 is 10, now add 10 to that to get 20 but accomplish it all in cell A1.

Is this possible either as a function or code.

I have not found the answer anywhere else.

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
1 1 1 1
2 2 2 2
3 3 3 3
. . . .
. . . .

there's a command button here that when clicked, it will open a new workbook
showing like below

this is the new workbook
1 1 1 1
3 3 3 3
. . . .
. . . .

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
Add Row In Sum Formula
I want to sum values in A1 to A6, so i use =Sum(A1:A6), i add rows aftere A6 what i shall put in the sum formula to include newly the added rows

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
Wanting To Sum All Amounts In One Column, From Criteria In Another Column
I have attached a work book, with worksheet Attendance which I am trying to calculate the sum of amounts in column N with the criteria from column C and E.
e.g Criteria Column C Jul-01 to Jul-31 or Sep-01 to Sep-30, Column E Alicia or Amelia.

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

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 + 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.


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

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
Add The Number In Columns Together
If I have a number in A1, and another number in B1, what formula should I use to add them together?

View Replies!   View Related
Sum The Columns
I am working on a project that I have the following information: ......

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
Copyright © 2005-08, All rights reserved