I have the following sheet with reg hours and ot hours. I need to insert a column that sums these two items. I used the macro recorder and it only works when the same amount of ID's or less. If there are more ID's it does not sum the remaining ID's. What it currently looks like:.........
View 3 Replieshow do I move the codes (1CH,1CL,1CP,1EM) from Column B to Colum A under Fee Earner cells where opposite the dates by using a macro?
I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.
A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX
I had thought an IF formula would be the way to go.
I have been trying multiple SUMIFS and SUMPRODUCT formulas to try and revamp my budget spreadsheet. My first criteria is the month being a certain number (contained in a column), if that condition is met it needs to look for the account number I am budgeting for (row heading) and if it matches both criteria I need it to sum the Column that has the row heading of the account number. I have succeeded in doing a SUMIFS and manually finding the column that has the heading of the account number I am looking for, but I would like this to be an automated process so I can copy the formula. Below is the data I'm using (a very, very small portion of it), and the results are how my budget sheet is going to look. These will be on separate tabs in the workbook.
EX: For July electric revenue (44211) I need my formula to: (if the Month column=7 & if the Account Code row heading=44211, sum the column that has the heading of 44211). So far I cannot get this to work.
DATA
Account Code
44211
44215
[Code]....
with the following problem : i have a matrix and a list with words and i need to delete every column from the matrix which contains one of the words from my list. I have a VBA script which uses entirecolumn.delete but i need one which doesnt rely on this function. The reason i can't use entirecolumn.delete is that it messes up all of my predefined ranges. here is the script i am using :
Dim c As Range, d As Range
Worksheets("table1").Activate
For Each c In Range("matrix")
For Each d In Range("listofwords")
If c = d Then
[Code]...
I have a table that looks like the following, only it's actually much larger:
_Red Blue Green Blue
A 2 4 2 3
B 5 2 1 1
C 3 1 2 5
D 2 3 4 2
As an example, I'm trying to sum all cells that match Blue and C. The answer should be 6, but I always end up with either zero or #VALUE.
Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.
However, someone high up in management in my organization would like to "drill down" into the data behind the
SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)
If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.
At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.
I previously posted a problem related to copying data from one sheet and paste it in another workbook when "Delivered" is chosen from a drop down menu. Everything works fine with my dummy files, but a problem comes up when I try to incorporate this code into the original file.
The issue is that once the code copies the info from the range A3:D3 it has to paste it into different cells in the other workbook.
Please see the attached file to see where I need the data from the range A3:D3 to be pasted.
i need a formula the looks for neame the gives the relivent data as headed by the colums under the wrd average
View 3 Replies View Related000108159900
0.97
000108160000
1.82
000108160000
0.38
000108160000
0.37
There is one value for 000108159900, which is 0.97 and three values for 000108160000. This is just a very small sample. I have about 12 million such rows. What I'm trying to do is create a separate column that will say 000108159900 has a total value of 0.97 and 000108160000 has a total value of 2.57. How can I do that?
I attached the extract. I am having the issue in the Total Workbook. Specifically, the Tax and Debit Column. If a company is on both workbooks (Insider Processor, Outsider Processor). The Tax and Debit should be charged once in the Total Workbook. But i don’t know how to get this happening
View 6 Replies View RelatedI have a column of numbers (each cell represents number of chicken eggs produced daily). Column data runs from K9:K415
I want a formula in column M starting at M9 whereby cell M9 sums up the total number of eggs for the week (i.e. sum of K9:K15), then I want to copy that formula down, such that M10 shows the sum total of eggs for the next week (which is sum of K16:K22) and so on. Thus summing up every 7 rows of data in column M.
I have been playing with the SUM and OFFSET functions but can't quite nail it.
Without having to type =SUM(D1,D3,D5,D7,D9, etc...), what is a quick way to sum, say, every odd or every even cell in a column or row?
View 9 Replies View RelatedCCTRCCTRCCTRCCTR112#112NB114#114NWSales group112112114114MONTHQTRYTDNielsen ID#NB##JAN109Q10920091/2/20092894.1717.161231.644704.9JAN109Q10920091/5/20093188.9544.22766.313039.35JAN110Q11020101/6/20092867.92945.571890.774520.44JAN110Q11020101/7/20091927.89210.58484.94960.61RESULTSTABLE 1TOTALS11211410961459742110595211857RESULTSTABLE 2TOTALS112#112NB114#114NW109608361199877441104796115623769481
Table 1 and Table 2 are examples of the results that I need - I have a formula that works for table 2, but when I need to consolidate the numbers as in Table 1 - it won't work - it only views the first 112 or 114 and doesn't sum them together.
the formula that I'm using for table 2 is:
=SUMIF(REV!$B$5:$B$574,"="&$A$50,INDEX(REV!$E$5:$DD$574,0,MATCH($C15,REV!$E$4:$DD$4,0)))
how do I adjust this formula to sum all 112's and 114's in row 3? it sums the columns good, but I need that extra component to make this project work.
I added an attachment. Column A is my "week number" and column E is my sales amount.
I'm trying to create a formula that sums the sales amount, only if it corresponds to a certain week number. I know sumif formulas, but can't get them to work well for this. For example, I want it to sum all the sale that happened in week one, then all the ones in week 2, then all the ones in week 3, and so on. There has to be an easier way than doing a sumif for each individual week and manually changing the week number in the sum if formula.
Could there be a character you can use in a formula to make the sumif week number "running?" So you could then just type in the formula once and pull it all the way down for the year, so the formula will work for the every week throughout the year?
I often look at utility data (start date, end date, use, and cost) and have a large list with up to several years worth of monthly data. I am trying to write a formula that will automatically sum the last 12 entries of "use" no matter the amount of lines; be it 12 or 112.
View 2 Replies View RelatedI'm working on a worksheet that has dates in one column (column A), and numbers in another column (column B). What I'm looking to do, is look through all of column A, find all entries between a certain date (ie Nov 1 07 - Nov 30 07), and sum all the values in column B that correspond those fields, as long as the value is greater than zero or not blank.
So, for example,
A B
Nov 1 1
Nov 6 -5
Nov 3 6
Dec 6 5
Jan 1 2
I would need the formula to return 7
I would like to sum G column (in my sample) that will meet a date criteria in C (like from 10/9/2009 to 11/9/2009) or similar. I tried it already with a formula =IF(c2=DATEVALUE("10/9/2009")=<("11/9/2009"),G2,0) could not make it work but its okay without the =<, or a single entry date (a sample in AB column), with this formula its being transferred to another column and being summed, what am trying to figure out is to make a formula or function that it can be incorporated in a sum at the bottom of G column if possible, I would be able to check how much cash collected in a week, days or month....
View 4 Replies View RelatedHow Do You Calculate A Column of names
Say I have starting on line 3 names of people in column D
What is formula to calculate all the names I type in this colomn D
Attempting to sum Book Values for Units.
Unit numbers in one column, Book values in adjacent column
Some Units have one occurence, others have multiple.
Some Book Values will sum up to 0 while most will have a positive value.
Attached shows small portion of data that presents range of issues I'm working with.
I have Data in Column a There in not demarcation between two Groups of Data
I want it Insert a Row between the two Groups of data.
Each group of Data end with a cell has "Employee" in it
The Row is to be inserted after the cell having “employee”
Look at the total in Cell A2, B2 and so on..... and multiply by the numbers below - then add them together at the end.
Problem im having is i cant seem to copy and paste the original formula as when i do it for the next row down it wont look at ROW 2, it will look at ROW 3, ROW 4 etc..
I need to sum all the rows, except the header row, of a column inputted by the user. The column will be located in a worksheet dubbed SourceSheet.
Here's a few pieces of code related to what I am trying to do. The header row is always row 1. So the rows I'm summing are always 2 - myrows.
I am working on a task analysis involving functions performed by different operators, each function marked by an "X" and totaled at the bottom of each operator column. I have seven sheets, named "Main Engine, Propulsion System, APU and ELEC.", etc. Columns B-F on each sheet are headed "C/M, Engr, Nav, LM, DE" respectively.
I need to add/total the bottom figure in each column (total of number of X's for each column) on a separate, final worksheet. Each of the worksheets contains a different number of functions, therefore the total of X's in each column is listed in a different cell# on each worksheet.
So this is my dilemma as the thread title states: I need to be able to sum/total the amount form different cell numbers (same column) across different worksheets into a cell in a separate, final worksheet.
I have figured out how to do this if the numbers are in the same cell across worksheets, but not varying cells by column.
I have data in 4 columns, A, B, C, D. Each column has 5 rows of data. I want to write a macro to automatically insert 2 columns, after each column in original dataset.
View 8 Replies View RelatedIs there a quick way to copying formula's from one column to every other column? I am using about 1000 columns.
View 6 Replies View Relatedsimple little procedure to insert columns on two sheets and call each by the same name. Macro runs off a command button and inserts a column at E:E and gives the user an input box with which to name the inserted columns.
Code: ...
How in excel do I get a no. to appear in a column dependant on a reference in a different cell. For example If i type March in a cell I want the number 1 to appear in the March column of a table.?
View 9 Replies View RelatedI have a sheet with products and information regarding these products. It is consistently build up in a 5-row information block per product like:
A1: name
A2: info
A3: price
A4: to be deleted
A5: to be deleted
What I want:
A1: name
B1: description
C1: Price
For product 2 it should:
A6 -> A2
A7 -> B2
A8 -> C2
A9 -> to be deleted
A10 -> to be deleted ..etcetera
The Macro should loop until no data is found in the A column. Note: some of the 2nd rows (information) are not filled. They can't be deleted but need to be left empty!
I would like to get the last value in column A & B and place them into another sheets cell. The number of rows will always be changing too.. In the attached spreadsheet I want the totals, which is the very last value in the colums.
View 8 Replies View Related