Based on the photo below i am looking to sum and multiple totals based on information presented.
I am looking to have the Total items calculated based on either the size run value(which in this case is 111, or 121, or 123,or 222 etc, all based on clothing sizes 1-s, 1-m,1-l) or calculated based on the Total from the sum of all the sizes (xs,s,m,l) which in this case is 9. and if that was not enough, then have the Total Items multiple based on the No of Packs, which in this case is 6.
In this example i have managed to get the formula to multiple 1 size run (111 = 3) multiplied by the number of packs (6) to get 18, (Size Run, is a drop down list from a reference page
I guess i need to know if there is an AND or OR function that can be combined with the SUM or SUMIF function.
I want to populate separate tabs from data in a master table based upon multiple conditions shown in the separate tabs. Each tab is a summary of a particular engineer's shifts over multiple weeks and their results on various projects. Each summary tab has the engineer's name and the number of the week (the two conditions). Example: On Aaron's tab, I want to show a summary of Aaron's work and his results over a period of several months, as well as who is working the other shift on that day for those projects. So I need to get the following information for Aaron's tab from the master table:
Week 1, Aaron is working the morning shift and Zeb is working the afternoon shift, Week 2, Bill is working the morning shift and Aaron is working the afternoon shift Week 3, Aaron is working the morning shift and John is working the afternoon shift etc etc
I also need to know how much work each engineer pairing has achieved in a particular week on a particular project. The engineer working in each shift may vary from week to week, as will their results. There is no formula for when a particular engineer will be working either morning or afternoon or whom they will be working with.
I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
I am generating a report from an accounting software every month and what I get is an excel spreadsheet, and one of the columns is a Date. The problem is that the date is presented in the format 20091012 and excel does not recognize that as a data. What I need is a formula or VBA code to transfer 20091012 into YY-MM-DD format.
I am wondering if there is way to analayse date and time data in a way that would allow me to look at time differences.
I have two columns of data, one has the date and time an entry was made onto a client database, the other shows the date and time an amendment or deletion was made.
What I would like is a formula that shows the time period difference between the two entries.
So for example if one column shows 14/02/2014 14:26 and the other shows 14/02/2014 14:28 it would show 1 (minute) as the result.
I have over 1000 entries to look at and manually it is taking a looooooooong time!
I've got a pretty large budget spreadsheet my department has used for about 5 years now. I want to add multiple totals for different accounts per sheet, however my problem is when I or someone else adds a row at the bottom(above the totals) it is not added into the calculation. I'm using simple formulas right now to total sums and also show remaing money in budget.
For example:
Cell F117 is the total amount spent "=SUM(F112:F116)" Cell G117 is my budget total, just a number Cell H117 is the difference "=SUM(G117-F117)"
I have a sumproduct formula that does a count of my table column based on 2 conditions. However, when I filter my data it still present the same results. So ive been told to use a Subtotal formula. I have scoured the net for the right formula and have come up with the following... though it returns data - it returns the wrong results.
Anything wrong with the below subttotal - sumproduct formula? for us in filtered data sets?
So I want the SUM of the total from a certain range of dates to go into the Week Columns on the right. I'm gonna manually select the date ranges for each Week but how can I set up a formula to Sum the Totals at the right of the table based on the Date Column.
I am both employed and self employed. so I have built a spreadsheet to keep track of expenses... but I hate having to scroll down to enter more and more expenses. So I have created multiple worksheets...
WORKSHEE 1 "INPUT" Expense Categories down COLUMN 1 Dollar Amounts down COLUMN 2 I would like to use this worksheet to just enter data from a receipt, then press enter and have this information transferred over to WORKSHEET 2 " TOTALS" on its own ROW. Once Enter is pressed, then enter in the same cells on WORKSHEET 1 "INPUT" for another receipt and once Enter is pressed, the data would transfer over to the next row on WORKSHEET 2 "TOTALS" and so on...
So if I want to see my totals... I just go to WORKSHEET 2 "TOTALS"
I have built WORKSHEET 2 "TOTALS" as follows ROW 1 is the SUM for each respective COLUMN Each COLUMN is its own CATEGORY from WORKSHEET 1 "INPUT" Each ROW would represent a new data entry. To be exact.....................
I need to make a sheet that give totals based on monthly figures from last year. Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75.
One month Last year the store made 31.82% on it’s money. The bonus for anything over is $75 per 10% increments. Ie 0.00% $400 0.10% $475 0.20% $550 0.30% $625 0.40% $700 So this month was over last months, 37.18% so the bonus was $3,975.
What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%. This hurt my head trying to get it right and im new to this more complicated formulas.
Worksheet 2 I want it list the Names only from highest to the lowest from worksheet 1. If two Names have the same total then I want it in Alphabetical order towards who will be listed first.
Example: Worksheet 1 B10 Sam AO10 10 B11 John AO11 14 B12 george AO12 16 B13 Steve AO13 9 B14 Carlos AO14 9
I currently have a problem with suming the costs from tables across multiple sheets where the tables are of varying size.
Each sheet, 'sheet1' to 'sheet10' for simplicity sake, has a simple table consisting of two columns, 'Item' and 'Cost'. Each row contains a certain item and it's respective cost. The bottom of each table then has 'Total' in the item column and the sum of the total costs in the 'Cost' column.
However, each table on each sheet has a varying number of rows. This means on 'sheet1' the total may be in B5 and on 'sheet2' it might be in B7.
I need to sum all the total costs from 'sheet1' to 'sheet10' in a single cell.
I tried using the =SUMIFS formula with a 3D Range but I discovered that you can't use this formula with a 3D Range.
The only solution I could think of is to use a loop that goes through each sheet in turn, searches Column A for 'Total' and then adds the cell next to it to a running total.
I am trying to accomplish is to associate each product on the Distribution tab in 'E' to its associated quantity in 'F' so I can count the total number of each product and display this in 'D' on the Totals tab. Once this is done, the total for each product would me multiplied by the associated price in 'C' on the Totals tab to achieve the total item value for each item in 'E'. I have included the current results and what the correct values should be. As you can see, I tried to use 'Countif' with no success. I could not find any solutions in the forum that were close enough to this situation to work.
Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc.
Row totals exceed the limit for Excel 2003, so I have split the data into different worksheets by year.
Problem is, now I can't create a useful pivot table, i.e. using 'multiple consolidation ranges' reduces the field list to "Row", "Column", and "Value".
I have 26 columns in each worksheet (all identical structures); some are multilevel factors, some are variables - and the way these relate to each other is unknown as this stage, so I literally cannot have the dimensions reduced in the way Excel proposes with multiple consolidation ranges.
I have a number of Grand Totals that equal to Hours of Work in a day ( Based on Demand from Customer Orders)
I only have 95 ( this will be a number in a cell that I want to be able to change if needed) work hours available to me each working day.
I want each day to attempt to fill in up to 95 hours , anything more and it will push the remaining balance forward into the other cells.
IE here is what I have for the next 5 days for Totals
211.3798120.304870.0099110.734375.041828.1292
Under the 211 I want it to change to 95 and then carry over the balance to the cell under 120 , I then want that cell to change to 95 and carry over its balance to the next cell and so on down the line. I will always have 22 Working Days I want to work with. So the last day may or may not have a greater then 95 total.
The 95 part I want to be able to change that to whatever number I think I will have available to me and it will adjust accordingly through the line.
Imagine I have 2 columns of information that look like this:
Column A Column B
AS Dog
AS Cat
AS Hamster
FT Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D Column E Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
I recently undertook an excel based knowledge test and whilst I haven't been informed of the answers/scores yet I have been frustrated with my inability to find the answer to what seemed like a simple question on the test....
Q: Without using copy and paste, put A1:H11 and its associated formulas and formats onto all 7 worksheets within the workbook.
Bearing in mind all questions on the test generally used only a few keystrokes, I felt fairly stumped and with copy and paste excluded from the options I am convinced I answered incorrectly. But the worst of it is I left fairly contently from the test and thinking I would get home and satisfy myself with the answer outlining a simple oversight on my part, have been shocked that I've been unable able to find one anywhere, is anybody able to put me out of my misery here as it's beginning to really bug me!
I have a file that is a master report. Every month i have to pull information to it from 40-50 different files (all of these are exactly the same and the data is in the same sheets columns and rows) Is there an easier way of just pointing excel to one folders so it would automatically pull the date into mu master sheet?
I have an excel file that contains 93 tabs, all with the same type of information, and I need to take the information from each tab and combine it so that it has all the information from all 93 tabs in one.....without having to copy and paste each tab,
I have worksheet A with cell AC2 to enter a team id# like 51922 for example. In Cell B3 I want it search sheet11 for the team name according to the number and put the team name in B3. Sheet11 has 11 columns the first one list the 613 team names, the remaining columns have #'s like the example giving for the teams. What formula would I need to perform this function?
This is a football game the other 10 columns represents 10 worlds with identical 613 teams to control in each world, but the teams have different #'s to identify the world, team, and coach.
I am having a problem filtering information within mulitple columns. I do not understand how the advanced search works either, so let me try to explain what is going on. I would post a pic of the table to make it easier to understand if I knew how to.I have thousands of rows in Column A with information, for this scenario a number. Thing is that all these numbers are seperated by many blank rows.
Ex: Row 1 has a number, Rows 2-10 are blank, Row 11 has a number, Rows 12-24 are blank, etc. Over in Column C, for every row that has a number in Column A, there is a corresponding number in Column C followed by Rows of numbers. ex: Row 1 Column A has corresponding information in Rows 1-10 of Column C.If this is clear, understand that this repeats for tens of thousands of lines. When I am looking for a specific number in Column A, I do a filter for just that number.
When that happens, it only brings up the info from that same row in Column C. I need the filter to bring up all ten rows of info in Column C that correlate to the row I filtered in Column A. I know the easiest way is to label all the rows in column A with the same number, but the reason the blanks are in place is so it is easier to read the breaks between the different information.
I am writing a macro that organizes data, but also needs to be able to conditionally copy data from other sheets in a workbook.
I have a table with values...column A has the identifiers (strings of letters) and column N has comments related to the data in each row. So, for example, row 3 column A has the value "AAPL," and row 3 column N has the related value "sells electronic goods."
Now, I want to be able to copy column N based on the value of column A into other sheets using my macros. So, say I have another sheet in the workbook and row 6 column A has the value "AAPL," I want row 6 column N to have the related value from the previous sheet. Is it possible to do this?
I created a drop down list of contacts for specific accounts:
Primary Second Third Fourth
When I click on each one, I want in adjacent individual cells their name, address, phone, and email. The results should only show up in the same row as per list choice.
This drop down list applies to 20+ accounts so each contact info corresponds to the accounts. A E F G H I So: Account... Contact Name Address Phone Email (DropList) And do that for all the accounts.
I essentially would need the Master Project Sheet, to have instructions to say. Research the folder of invoices, when you find project number 001 and plumbing (job performed), then grab C1 and D1 off of the contractor invoice and place it into the corresponding project name on the master, in the case above B2 and C2. Is that possible?
I have data that I copy from one sheet and move it to another. If I know I'm parsing data that may vary in amount of line items but will always be across 3 columns is there a way to parse this data evenly.
for example: I would like to look in column "D" and if there is an "A" I would like to parse cells A,B,C in that row to sheet 2 and parse this information evenly across 3 columns evenly across columns "A,E, I" In my example the data is only 21 rows of information the problem I have is evenly parsing this information.
I work in HR MI and am compiling an equality spreadsheet. Out of everyone who is on the list I want to see how many men and women we have which I have figured out using = SUMIF(GENDER,"MALE",NUMBER) but from that I want to say theres 50 male in total and out of that 30 are White and 20 are Indian. Is there anything I can add to this formula so it looks at more than two defined sets of information??
I have been struggling hard to pull information out of a table using vlookup and match & Index functions. It does not seem to be working. Below is my criteria:
I have a data of call centre agents with names , dates and calls offered answered details. On another sheet i have given a list of names of agents in a drop down list and all the dates of the week. Now i want that when a user selects a particular agent name from the drop down list, the data fetched under '07042014' date column is against the number of calls answered by the selected agent on this date. I have 5 columns of title '07042014' , '08042014', '09042014', '10042014' and '11042014' Capture.PNG. Attach is the file how it looks like.
How to use match and index function or any other function to fetch information against this date as per the agent selected.
