=COUNTIF(F3:F38,"=VL") This works for totaling the VL in the cells in the column but what if I need it to add DVL in the same column to that total? So I need it to add both VL AND DVL as one total. I can make it add one or the other but not both
VL DVL
VL VL
VL
VL
VL
5 2
I would like to have a macro which adds all the numbers in a column. The problem is that sometimes there are less sometimes there are more numbers. What I would like is, that the macro to sum the numbers right under the last cell which contains a number.
View 2 Replies View RelatedI don't think there is such a function but is there a way that I can have a function that constantly adds the numbers in a column and when the sum of 240 is reached it adds 30. I need it to do this only once and not keep adding at every 240 point.
View 2 Replies View RelatedHow can i do this using Excel 2007. I have to cells, A1 and B1. A1 is always Positive whereas B1 might be Positive or Negative. I need a calculation/formula in C1 that ADDS cells B1 and A1 (B1+A1) IF B1 is Negative and SUBTRACTS A1 from B1 (A1-B1) IF B1 was Positive.
View 3 Replies View RelatedI have written a macro that copies a row to a new sheet and adds a formula to that copied row.
View 8 Replies View RelatedI have a Macro that finds in Column F duplicates and adds an Alpha Character (A-Z) to the last right position in the filed.(1-12 Alpha/Numeric) .
It's set to check all rows until a specific number of rows are reached. The current code does this until intCount and intRow equals 1500. The number of rows vary in each file.
i had a basic spreadsheet for my incomings and i got wiped with my hd exploding!! i know how much the total was i just need a way of creating a list of random amounts within a specified range over a year to give to my accountant.
I am trying to organize it in a way that I can have all my purchases added based on my text criteria of certain purchase... Ill try to be as clear as possible. I have already organized it in a way that when I paste new months statement in my Excel file it sorts it by date, newest on the top, and I made Conditional Formatting by color on column D which states the Description of purchase. Starbucks appears in description cell but it is followed by number and location making each Starbucks cell in column D different in name. So its hard to use Exact phrase formula to combine the amount for that purchase.
My question is: Is there a formula that find a PORTION of text in a D column and automatically adds the price to the right of it together in one cell. There is a formula that looks for the whole text in the cell but that doesn't work when my name has always some extra numbers at the end of the word Starbucks
I entered exactly 113,876.92 in cell L16 I entered exactly 113,390.02 in cell L17 I entered =L16-L17 in cell L18 L18 incorrectly shows the result at 486.9000000000009000 (note the extra "9" after the 11 zeros). When I expand the viewable digits on L16 and L17, they have ALL zeros after the cents. (I went out at least 25 digits). I can't be the first one encountering this.
View 4 Replies View RelatedI want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used)
(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
I'm providing a tool for users that:
- allows the user to select Y / N for multiple criteria
- based on the criteria, calculations are made
- based on the criteria and the calculations, totals are calculated
Here's the formula that's currently NOT working for me . . .
=IF($M8="y",$M12*V$9,IF($M8="N",0)+IF($N8="y",$N12*V$9,IF($N8="N",0))+IF($O8="y",$O12*V$9,IF($O8="N",0)
+IF($P8="y",$P12*V$9,IF($P8="N",0)+IF($Q8="y",$Q12*V$9,IF($Q8="N",0)+IF($R8="y",$R12*V$9,IF($R8="N",0)
+IF($S8="y",$S12*V$9,IF($S8="N",0)+IF($T8="y",$T12*V$9,IF($T8="N",0))))))))
Currently, returns a value of '14' -- shown in the visual provided, should be a value of '21.'
all the data from the form is successfully placed into the assigned work sheet.
My question is
What is the code i have to Assign so it automatically adds a row down?
right now it adds the info but every time I try and put something new it overrides what was placed before..
I'm working on a spreadsheet that includes items I sell as well as the packaging it goes in.
The formula I'm looking for basically will tell a cell that if one cell (# of box) is equal a certain # it will add two separate cells by the weight of the box.
I have cells for each of the following: Weight in lb and weight in oz of the item (no box), a cell which holds the number of the box (ranging from 1-15) and two cells with weight in lb and weight in oz which would be the total weight of the item + box.
So, for example, if weight of item equals 1 and box cell equals 2 then it will put the weight of item + weight of box into a third cell.
Here's a picture of my current spreadsheet : [URL] .....
Basically, I would like to do something such as if P3 = 5, then Q3 will equal N3 + U6 and R3 will equal O3 + V6.
I have a spreadsheet for my local camera club that needs a few "IF" formulas that I have not a clue at how to accomplish. I have done the rest of the spreadsheet.
The formula needs to be
IF cell A1 = 22 or 23 or 24 (a manually entered number) then the answer = 1
IF cell A1 = 25 or 26 or 27 (a manually entered number) then the answer = 2
Also need another IF formula that adds up various cells: A1 + A2 + A3 + A4 and then adds the same cells on a 2nd and 3rd worksheet, and if the total is zero, then the cell will remain blank in the answer field, rather than having a zero in it.
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.
I have a macro launched from a command button that sometimes puts a bar at the left side of the window. When it executes the line Sheets("graph").Select the first line in the macro it puts the bar at the right side of the window. The characteristics of the bar are:
1) It is associated with the main excel window - if you minimize the window containing the worsheet the bar is still visible
2) It is one of two widths, about 1/4" or about 2.5"
3) If you move an Excel toolbar to the left side of the screen the bar disappears. If you move the toolbar back to the top of the page it doesn't return but it can appear the next time you run the macro.
4) It only appears some times you run the macro - I can go days without seeing this problem and then it happens every time I run the macro.
I currently have the below in my VBA which adds the text to the subject of an email:
Application.Text(today - 1, "MM-DD-YY")
today = Int(Now()) is part of the macro. How do i format this to display workdays only.
For instance if today is 07/21 (monday) i need this to display 07/18 vs 07/20.
In excel that function would be =WORKDAY(A1,-1) where A1 is =Today(), just not sure how to translate this to VBA.
i have a list of parts that adds up and shows a total at the bottom (great, works perfectly)
in the next column i have a percentage (margin) which again works perfect
ie.
Col A Col B Col C Col D Col E
1 ENGINE 4500 5000 11%
2 SURCHARGE 1800 2000 22%
3 OIL 1 2 100%
Totals 6301 7202 ??%
what i would like to do (forgive me if i have missed something) is for the total value in colum D to add up but for the % in column E not to be included in the total %
Is it possible to have a situation that when the word"SURCHARGE" is in the row then the % will not be added to the total %
In other words i need the surcharge price to be included into the final price, but the surcharge would give me a false figure so that needs not to be included in the total %
People usually keep track of their own points, however an official would need to confirm that they are correct before approving a purchase. However 99% of the time, people have the incorrect point totals.
I am currently working on a book to automate the system. It means players wouldn't have to keep track of their own points and that the point totals are always up to date. The first sheet in this workbook is a summary page which has player names, points from posting, bonus points, level up costs, purchase costs etc. Then there is a database with all the costs, which I pulled from the site. Then there are numerous record sheets, which have the player name, the item bought/levelup purchased, and the cost, which is pulled from the database via vlookup. Here is where it gets irritating.
On the summary page I attempted to use SUMIF in the total fields (total cost of purchases, total cost of leel ups etc). It would basically search the record for any entries with the name matching that of the player, and add the amounts associated with that entry. However it would always return 0. After doing some playing around I came to the conclusion that whilst the VLookup returns the correct values, functions like sum see them as 0. So instead of it going 5+7+7+10=29, it says 0+0+0+0=0.
I have the following code which adds a consecutive digit to a repeating cell value in:
=a2&countif($A$2:A2,A2)
So, this formula has put in cell B2. This means, that if if A2:A5=1 for each row, then B2=11, B3=12, B4=13, etc.
What I'm trying to do is put "-" between the original value and the extra digit. So, B2 would instead look like 1-1, B3=1-2, B4=1-3, etc.
AVERAGEIFS(X$2:X$1000,$AT$2:$AT$1000,1)& TEXT(BE2/BD2," 0%")
The formula is working , but the result is : 3.50239234449761 58% Formatting the cell did not work. Can these decimals be slashed?
I have a really strange issue on one machine. The end-user gets Excel reports emailed to him. When he opens certain ones, there is an extra space (almost a line without borders) on certain rows. This only happens on some of the files he receives
AND
This behavior is only on his system. On all other recipients' machines the spreadsheets do not have the extra space on some rows. It does not matter if he opens the attachment within Outlook or saves and opens in Excel from the save location.
I have tried resetting the defaults and that does not work.
I need to create a function that adds a predetermined amount (lets say 120) to a cell (lets say A1) every time another cell (lets say B1) reaches a 10 (like 10,20,30,40 etc)
Lets say the B1 is 30, i need A1 to recognize that it needs to show 360 as its value (adding 120 for every 10 of B1)
I have a market data linked to excel through DDE link so prices are updated real time.
Cell A5 contains last traded volume which updates whenever a trade is done.
I would like to SUM/ADD all last trade volume in cell C5. Is there any function or macro can help me to do this?
I have the following extract of code which is meant to add a chart. However, it adds a pivotchart and then errors on the last line because it can't change the source data on a pivotchart like it can on a non-pivotchart. All of the other charts in the workbook are pivotcharts. It was working fine last week.
Sheets "LOI Sheet" and "Data for Charts" exist.
"e" is a defined integer with value 53 at last running.
Charts.Add After:=Worksheets("LOI Sheet")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Data for Charts").Range("D2:D" & e), PlotBy:=xlColumns
How would I write a formula that takes cell values from six worksheets and adds them up as a percentage (quarterly updates)? I realize this is wrong, but here's what I'd tried (for three sheets).
=+SUM(IF(EVH!F7="Y",25,0)+IF(FUN!F7="Y",25,0)+IF(HES!F7="Y",25,0))/3
how to create a function in VBA that works exactly as SUMIF, except it will also add a comment in the cell that references the formula, where the comment would be a list of the individual cells being added? Basically the comment would read "2+4+5+6" or "2,4,5,6" or something along that.
View 1 Replies View RelatedAnyone know how to create a line or two to move all workbook sheets into alphabetical order from left to right?
View 5 Replies View RelatedI have a macro which opens a csv file, moves a few columns about then pastes the data into a master workbook. The problem I have is that even though there is only around 1,500 rows of data on the csv file, the sheet on the master workbook is showing up to row 1,048,576 after the data has been pasted in! This is causing issues as I use this data later on in the macro and there are a couple of 'find and replace' commands that take an age to run because of the number of blank rows.
Code:
Workbooks.Open Filename:= _
"L:PVDDBB15." & Format$(Sheet1.Range("G5"), "ddmmyy") & ".csv"
Columns("AB:AB").Select
[Code].....
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
