I'm looking to create a formula that I'm not sure is possible. In my spreadsheet template I have company departments in Column C4:C500 and projects in rows F3:AZ3. The actual proj ID in F3:AZ3 will vary by spreadsheet, but I have a separate Master sheet that has all projects and all departments listed in a pivot table-like format. Since there are far more proj IDs than departments, in this master list the depts are all in the same row and proj IDs in the same column. Due to the input, I cannot change the template setup.
I will be pulling many spreadsheets together and want to calculate the total where the company dept and proj matchup.
The SUMIFS statement I am attempting is =SUMIFS(C5:AZ500,"Project",cell w/ project,"Dept",cell with dept) - but the issue is you cannot do a sumif using multiple columns.
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4 89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run. 2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L 3. In Column D, I want to specify 3 criterias namely A, B and C 4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo 5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G) 2.) Multiply the % by the Revenue (Column H) 3.) Sum up the total for each of the names (Locatedin Columns B, D, F) 4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
I am trying to create a formula that will populate a cell with text depending on an entry in 7 other columns. Only one of the seven columns will have an "Yes" entry. This is what I have so far that is not working:
So if there is a "yes" in column C, I want "Injury" to be displayed in my destination cell; if column C is blank but there is a "yes" in column E, then display "Property Damage" and so on. I am completely flummoxed.
I would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.
I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).
For example in cell M7: =IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0))) CTRL + SHIFT + ENTER
I'm trying to create a short script which performs the following:
Searches for a specific name in column C on my "Resource List" sheet, then When this is found, check column H to see if this is populated,Then when both criteria are met delete the entire row.
Using a link I found here [URL], I've put together the code below:
[Code] ....
The problem I have is that although the criteria are being met the row is not being deleted.
Macro below would be to only go down to the cell = to the bottom-most cell with data in column A.
These columns below are all in row A (the title row) If column named "paper" says "white" OR "grey" AND column named "pizza" says "mushroom" AND column named "size" says "large" AND column named "flavor" says "butter pecan".. (again, only going down to the same matching cell with any data in column a)
I have a data ranging from J:EW with a heading in row 2 e.g. J2 the heading is "OT1 Hrs"; K2 the heading is "OT1Amount"; L2 the heading is "OT2 Hrs" and so forth. My difficulty is, it is too much multiple columns from J to EW, how can I draft the vba to hide a column that has "Hrs" in row 2 heading instead of doing it one by one.
I am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data.
I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "-". Column C contains a dollar amount.
1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "-" in Column B.
2. I need to be able to SUM the $ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "-" in Column B.
Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.
I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.
I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).
Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.
Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.
The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?
I've not used SUMPRODUCT previously and can't understand how to get results for the attached.
I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.
I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.
The second table is actually in a different sheet but is the source of the data I need added.
Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'
Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)
I posted this on Mr Excel, but did not get an answer. I hope someone here can help.
I have a rather large financial worksheet that I am trying to extract some specific data from.
The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.
Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......
With me so far?
What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.
In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.
Just in case that is not totaly clear----
criteria to meet
Column G = "Project Code" Column J = "Exp" Row 2 > this month Row 3 = Forecast.......................
I am looking to write a search function that searches through a whole document. The only examples I can find are of a could different search functions but they all are searching an specified individual column and even then I didn't really understand them. My worksheet has 6 different columns which are all different labels for a certain tool. I want the user to be able to input any of those 6 labels and have all the information in that row be returned.
Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.
I want to be able to look at two columns and set a criteria to count. I want to look at column A and if its blank then look at column B and if it has a value of more than 0 then count.
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.
I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.
I have a spreadsheet with multiple columns. The first column defines a "route", and the next two list "start" and "end" cities for that route. The fourth column lists the length of each route. There are only a limited number of cities, so the same entries appear in both "start" and "end" several times. I would like to use Autofilter to sort the list for every appearance of a given entry in either "start" or "end". Is there a way to make Autofilter sort mutiple columns simultaneously?
I could achieve the desired end result with Advanced Filter, but I want something with the ease-of-use and immediate update/response of Autofilter. Advanced Filter requires explanation (as well as lots of clicking and typing) whereas Autofilter is self-evident. I also want to avoid VBA Macros as they are not well-understood by the users who will use this spreadsheet (and any VBA Macro will require very specific input to work properly.) Is it possible to do what I want? Or is Advanced Filter / VBA the only way to do it?
I cant get this one off the ground. Normally I am pretty good to find bits and pieces of advice from other posts and put something together, but this one has me stuck from the get-go. I want to create several lists based upon the row(s) containing at least one set of criteria (day and time, contained in two differnt columns). Each row allows for up to 3 sets of days and times (i.e. columns B and C, columns D and E, and columns F and G) Each row is date and time stamped as to when the data was entered in that row (column H).
I want one list for each set of the various day/time combinations allowed in columns B thru G. The list should provide the name(s) contained in column A, in chronological order based on column H, when a criteria match is made in columns B:C, D:E, or F:G. This is far easier to understand with the attached sample. The lower section of the sample represents my desired output that I cannot seem to achieve.
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted. The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
