Multiplying And Adding Multiple Cells To Get A Sum
Feb 18, 2014
I have a spreadsheet with 4 columns.
A, B, C, D
I want to multiply A*B and then add that to C*D
=SUM(A*B)+(C*D)=TOTAL
However, when I dont have a value/number in A or C I get a #VALUE in the total cell. It will only calculate properly when I have numbers in all 4 cells.
Is there a way to set it up so it will calculate if cells A or C are empty? I dont want to have a zero in these cells - just leave empty if no value.
Also - when the total cell is empty I want it blank.
View 13 Replies
ADVERTISEMENT
Mar 21, 2014
I am creating a spreadsheet for some deliveries. The pieces I have are all in feet and inches and multiplied by a certain quantity. I need a formula to run this and give me feet as an answer.
I have 4 cells, in A1 I have feet without the foot mark (20) and in cell B1 I have inches without the " (5). In cell C1 I have 20. How do I write a formula to do the following:
20'-5" multiplied by 20 = Answer in D1 408'
View 2 Replies
View Related
Feb 5, 2014
I have a total number of hours I want to subtract from. Example in cell e3 I will have 800 hours. In cell f3 I have the number 2 (to represent 2 workers at 40 hours, so that should total 80). I then want f3 to multiply by 40, then that total subtract from the 800 hours in cell e3. Basically I want to be able to put 2 workers in a cell, have that multiply by 40 and minus from the 800 hours in cell e3. I want that to carry on for cell g3, etc.
View 1 Replies
View Related
Dec 22, 2008
This is about my misformatted download again. It considers all the numbers to be text. Naturally, I need them to be numbers. I discovered a page on the Web that says if you multiply all the numbers by 1, suddenly Excel gets a clue and figures out they are numbers. I've tried this (as the page recommended) by using Paste Special, and it works. I know the command I need to use to get it to Paste Special:
View 3 Replies
View Related
Jan 20, 2009
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:
A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
View 5 Replies
View Related
Oct 12, 2012
I want to merge some cells using a criteria. To be more specific, let's speak on the file attached.
Beginning with the P5 cell I want to merge to the right groups of 4 cells for each name from the column J. Therefore the first group of merged cells will be P5:S5, the next group will be T5:W5 and so on until reaches 11 groups of 4 cells. The last group will be BD5:BG5.
I tried a script like this but it says that there is a syntax error on the "Else" instruction:
Range(Range("AB5").End(xlToRight).Offset(0, 1), Range("AB5").End(xlToRight).Offset(0, nrCat)).Select
VB:
Dim i As Long
nrCat = Range("O2").Value
For i = 1 To nrCat
If Range("AB5") = "" Then
[Code] .....
View 9 Replies
View Related
Aug 17, 2009
I have over 500 countries with unique shipping costs associated with each... each of them has the same mark-up price... 2.25. I am hoping to be able to insert a formula that will cover each country... i am currently entering each one indivdually (E10*2.25), then the next one (E11*2.25), etc.
View 5 Replies
View Related
Nov 5, 2008
Here is what I have In cell H4 I put the following code =B5:G5
But when I add number in thous cells nothing happens. I want cell H4 to keep a running total of cells B5 through G5 so as I add numbers to the cells I would like the total in H4 to change.
View 2 Replies
View Related
Nov 3, 2008
Let me explain this as best I can:
I have an excel file with multiple tabs on it. Each tab has the exact same format with different numbers. On the last page I want to add cells from each tab and have the sum go to a cell on the last tab.
View 10 Replies
View Related
Sep 3, 2013
This is my text:
Test Name1
Test Name2
Test Name3
Test Name4
Test Name5
Required result is:
Test Name1|Test Name2|Test Name3|Test Name4|Test Name5
View 8 Replies
View Related
Jul 7, 2009
I have column of cells containing entries such as V1, V3 and V7. I'm trying (and failing) to come up with a formula to separate the numbers from the letters and add them together: V1, V3 and V7 would together give 11.
I can use the MID worksheet function to separate my numbers from my letters on a row-by-row basis, but I can't work out how to do the whole thing in one fell swoop. =SUM(MID(A1:A10,2,2)) doesn't work, for example.
View 9 Replies
View Related
Nov 20, 2009
I have data for 500 companies over 10 years, in three criterions: “EPS”, “DPS” & “PX” (i.e. earnings, dividends and price).
For each company I have four lines recording the data, and given a letter code indicating how the change in dividends and earnings have been according to the following
(Examples)
DD-ED = Dividends Decreased & Earnings Decreased
ND-EI = No Dividends & Earnings Increased
As can be seen below this code is present for each company
What I need help with is some sort of lookup function which takes the average of the price changes (which is given in numerical values) only for the companies which code is equal to the code in the reference field.
So something like: =AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353"",D2:D2353))) (currently counting every 5th line, since there is 4 lines between respectively PX/DPS/EPS for each company) –But with a constraint indicating only to include the value in the calculation if the codes are the same (e.g. "DI-EI" = "DI-EI").
So that the value, which is currently 20.96% (which now includes ALL price values), would only include those for the respective group (in this case, companies in the DE-EI group).
View 9 Replies
View Related
Feb 26, 2013
I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.
I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.
View 4 Replies
View Related
Apr 10, 2013
i have data stored like:
col1
XXXX1244 50
XXXX1519 60
XXXX1244 50
xxxxx1111 10
xxxxx1519 65
the last 4 caracters are numbers. I need to test these numbers and sum the corresponding values them in a single cell without adding new column(SUMIF like).
so in the above example I need to sum all ending at 1244 or 1519, therefore the sum showed in the single cell equals 225
to extract from a single cell: =VALUE(RIGHT(D8;4))
I tried to use an array formula but it seems to crash if a blank cell is in the array
View 5 Replies
View Related
May 31, 2013
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
View 3 Replies
View Related
Jan 8, 2014
I have a column that looks like the following and I need to add the numbers:
27 skids
31 skids
56 skids
13 skids
The unit "skids" is constant. The answer I am looking for is "127" or "127 skids"
View 3 Replies
View Related
Mar 28, 2008
I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
View 14 Replies
View Related
Oct 1, 2009
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
View 5 Replies
View Related
Oct 2, 2013
I have a drop down list with 'Weekly', '4 Weekly' or 'Monthly' as the selectable values.
I want the cell below to calculate a number in an adjacent cell with either 52 for weekly, 13 for four weekly or 12 for monthly.
I.e 100 and weekly would display = 5200 ( 100 x 52 )
100 and monthly would be 1200 = ( 100x12 )
View 2 Replies
View Related
Jun 8, 2009
I have been trying to multiply cells(6,3) of sheet 'Input2' with the 23rd row of next sheet 'Input' from 4th column in that row to last used column. Then I want this looping to go from 23rd row to the last used row in that sheet. But I am not successful. The code that I tried till now is:
Sub test()
Sheets("Input").Activate
x = Range("D23").End(xlToRight).Select
For j = 4 To x
m = Sheets("Input2").Cells(6, 3) * Sheets("Input").Cells(23, j)
Sheets("worksheet").Activate
Sheets("worksheet").Cells(j, 17) = m
Next j
End Sub
View 9 Replies
View Related
Apr 7, 2014
Basically I have 1 value on 1 sheet and and multiple values on another sheet and I would like to know if it is possoible to mutliply these togther i.e
Sheet 1
100
Sheet 2
2,5,9,15,20....
I would like to calculate (100*2)+(100*5)+(100*9)+(100*15)+(100*20).....
I know if sheet 2 only had a few numbers like above which I have used as an example I could link the sheets; however in my data, sheet 2 is about 75 numbers that change.
View 3 Replies
View Related
Feb 9, 2010
I have these two formulas I am trying to multiply
=ROUNDDOWN(AK170/Sheet1!B3,0) Returns a value of 2
=ROUNDDOWN(AL170/Sheet1!C3,0) Returns a value of 7
I need to multiply these 2 formulas together to return a value of 14
View 2 Replies
View Related
Jan 6, 2008
I have tried to put together three simple formulas for a very simple problem. I wanted to string together three vlookup formulas which were as follows;
=vlookup(F4,prices,6)+vlookup(D4,floorpremiums,2)+vlookup(G4,aspectpremiums,2)
The first lookup would work but as soon as I added another it would result in #n/a. I tried adding the suffix "false" after column no. but that did not work.
On the second lookup I did mix text and numbers e.g the first row in the column had a "G" in it and the rest were numbered 1-10. The second two vlookups were on a seperate sheet but in the same workbook.
I have checked the named ranges and they work fine when done individually but it seems to be the combining which messes things up.
View 9 Replies
View Related
Aug 15, 2006
I need to multiply the values of 10 rows by the values in 10 columns and sum then, akin to the sumproduct function, which only seems to work if the values are all in rows or all in columns.
In other words;
rows of 1 2 3.......10 & columns of
a
b
c
.
.
j
Expressed as (1*a) + (2*b) + (3*c) + ....... (10*j)
I can do it long hand and I can do it by transposing the data as an intermediate step, but I was looking to do it all in one formula.
View 6 Replies
View Related
Jan 29, 2013
I would like to map each item in the row with the column header and the price for that item for the customer, similar to the below one, go through both the work sheets, i want the output in the rowitem view.
SKuIDItemNameCustomerRate
11682Item 1Cust 1312.56
11682Item 1Cust 2411.27
11682Item 1Cust 3411.27
[code]....
View 2 Replies
View Related
May 30, 2014
I have to complete the following project in visual basic for Excel
direction quantity price
b 400 10
b 100 20
s 900 15
So the above is a small sample space of what i have(over 3000 rows in total) they are stocks. if there is a "b" in the direction that denotes a negative (short) and if there is an "s" (long) that denotes positive.
Two parts:
1) Write a traditional macro (with button) that when pressed, will give the total Net value in a message box.
2) Secondly, write the code in 'class module' that give me the over all net value in a message box.
I understand what I need to do but just don't know how to write the code for this in visual basic.
So for the above example the net would be -400(10) -100(20) + 900(15) = +$7500
But how can I write a program to do this and display the final answer in a message box?
Exercise.xlsm
View 14 Replies
View Related
Sep 22, 2013
I need a formula for H2 to multiply the value if it is in any one of the cells in B2 - B6 by the appropriate value to calculate the annual total. Such as if the number is in weekly column it would multiply by 52, fortnightly 26, monthly 12, quarterly 4 and annually 1.
Weekly
Fortnightly
Monthly
Quarterly
Annually
Ann Totals
Mortgage
1000.00
Council Tax/Water/Sewerage
1000.00
View 2 Replies
View Related
May 6, 2008
What is the SINGLE formula for getting Value by given volume and price individually for more then 2 products?
View 4 Replies
View Related
Sep 1, 2008
I'd like automate consists of entering 56 numbers (up to 3 digits, 000-999) and finding the difference between today's numbers and yesterday's numbers. Then multiplying each difference by a set number (i.e. 1, 2, 3, 5, 10, 20, or 25). These numbers may, from time to time change (i.e. the 32nd entered number may be multiplied by 3 for 2 months, then we might swap out a product and then need to multiply by 5, etc). At the end, I need all 56 differences multiplied by their individual respective multipliers totaled. This needs to be done daily using previous days numbers and be able to change the multiplier without effective previous data.
I was trying to to this myself by Column 1 = multipliers, Column 2 = yesterdays' numbers and Column 3 = today's numbers. The calcualtions are done by excel and a total is summed. This I can do. However, each day i'd need to make a new file and enter yesterday's and today's numbers. This is time consuming. I'd like a system where I can just entere today's numbers and any changes to the multiplier and it will spit out the total.
If anyone can guide me or point me in any direction to accomplish this automation. I would greatly appreciate it. I'm pretty computer savvy, but just have not had the time to dwell into Excel.
For those interested in what this is for: I have a small business that sells instant lottery tickets. Each day we have to figure out the number of tickets sold the previous day. We have a notebook and manually write down numbers, do the substractions by a calculator and total. The table in the notebook is seperated by ticket values (i.e. $1 tickets, $5 tickets, etc). However, certain tickets run out or occasionally we need to move tickets to a different spot, thus the MULTIPLIER in the automated system would need to change.
View 12 Replies
View Related
Jun 3, 2014
I am trying to put together a basic sheet with what I think is basic math, however, the multiplication does not seem to be working properly.
Field 1 is land value, field 2 is total acres. The math I have in field 3 is to give me a value per acre. Here is that formula: =Sum(B3/C3)
I have set the field to a number field with two decimal places.
Field 4 is Total Dry Acres field 4 is calculated to give total value for dry acres (here is where I am having problems) Here is the formula: =Sum(D3*E3)
Here are the numbers: Field 1 = 218702 Field 2: 1536.51 Field 3 (calculated): 142.34 Field 4: 1171.75 Field 5: 166791.54
It all looks good on the spreadsheet, however the field 5 number is not correct.
Break it out on a calculator: 218702 / 1536.51 = 142.34 (rounded)
Now 142.34 * 1171.81 = 166795.44
so working on the calculator, there is a difference of 3.69 between the two.
View 5 Replies
View Related
