I have a long list of past jobs, around 4000+. I have multiple fields, but I really wish to concentrate on the:
1) Job Type - Example would be Medical or Imprint
2) Job # - Correlates when it was done - Example would be 91059 would be a job within 2008-2009 fiscal year.
All the jobs are listed in the first spreadsheet. The second spreadsheet will hold generalized data broken down by the Type of Job and the fiscal year it was done.
I wish to first count how many jobs fit a specific job type. This was easily done:
=COUNTIF(Data!A2:A4121,A7)
A7 = Medical
A1:A4121 = the range of the names.
Count comes up as 346, which is correct.
I then want to add another parameter to break up the 346 by fiscal year. It ranges from 05-06 to 08-09. I came up with this:
Since the job # correlates to the fiscal year it was created, anything starting with 9 is a job done in 08-09. Anything starting with an 8 would be of course 07-08 range. I would have multiple fields with each different fiscal year.
However, when I put the 2nd formula in the function didn't work. It keeps the count at 346, which I know is wrong. I am not sure if I did something wrong here. Been looking at this for an hour and can't figure out what is wrong. The jobs that are medical within 08-09 fiscal year should be 120, but it keeps at 346.
Im currently using the below formula but was wondering if its possible to combine the parameteres that im looking up in coulmn L in sheet one so i dont have to do the formula three times? something along the lines of={"P11111";"P22222";"P33333"}?
In the attached file I have a sheet containing my data in A1 - D73. Column A contains a list of names, Column B contains a specific month, Column C contains a specific category and Column D contains the raw data.
Is it possible to create a formula similar to VLOOKUP to look not only at Column A, but to look at Column B as well in determining the value returned? I would like other users to first select a name and then select a month to view the data. I've attached a sample of what I've created so far. The original file contains 14 Names, 9 Months and 40 Categories.
I would like to have set of data as Parameter if possible.
Example: In Microsoft Queries I have two data sets. In first one shows let's say Item Number, and second one I should have table with components of all Item numbers listed in first table.
So first table I set one Parameter (?) and i get like 8 results. I would like that second table takes those 8 results (sometimes more or less then 8) as parameter and shows data (components) for them.
If I set sign '?' I can only select one cell as parameter, is there any way I can select multiple cells as parameter and use it like SQL function IN ?
Code: SELECT 1.ITMNR, 1.COMNR, 1.QTYPR FROM 1.1.1 1
WHERE 1.ITMNR IN ?
And second table should look for results in first table under 1.COMNR and select it as 2.ITMNR
Code: SELECT 2.ITMNR, 2.COMNR, 2.QTYPR FROM 1.1.2 2
how to pass multiple parameters using a range of cells to MSQuery? When I try to it tells me that I can select a single cell only. Anyone know of a new and improved sql driver to use with Excel?
I get about 10 text files at the beginning of each month that I need to import into Excel. The file layout does change slightly from month to month so I have an Excel spreadsheet with the layout as the first worksheet. Each text file is in fixed layout form rather than delimited. The structure of that page is that the name of the first text file is in cell A1. The cumulative field widths are below that and the data formats are next to the field widths (in the adjacent column “B”). T=Text, G=General, D= Date with 3 flavors of Date: YMD, DMY and MDY. For the second text file, it repeats the structure in columns “C” and “D”, the third text file in columns “E” and “F” etc. What I would like is for the macro to open the first text file (based on the name in cell A1) and import it into a new worksheet (same file) using the cumulative field widths in A2 through A8 and the formats from B2 through B8, then import the next text file to a new worksheet (same file) using the cumulative field widths in C2 through C8 and the formats from D2 through D8 and keep going until all the files have been imported. The sheet names should be the same as the file that is imported (the “.txt” part of the file name isn’t important). I’ve attached a copy of the Excel spreadsheet that has the file names, field widths (cumulative) and field formats and a 2 row sample of the text file for the first import.
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
I am trying to create a formula that will tell me how often our sales reps are going to specific accounts. I have a master list of each persons accounts and next to each account name there will be a cell with a formula in it. This formula will look at the cell next to it, "the account name", and then count how many times that name comes up over each months sheet. I was also wondering if I can then get the average over 12 months in the same formula. If not thats ok.
The problem I am having is that I keep getting an error #VALUE!. I had no problem with it looking at only one sheet, but when I do more than one it gives me an error. I think it is the was I am breaking up each sheet. I have tried to do it every way I can think of... ,;) Nothing seems to work.
I have a workbook with alot of sheets, each sheet is identical in format. What I am trying to achive is a way of counting all the occurances of a name in a range of cells on all sheets.
To try and explain, each sheet has a drop down list in cells C5:V5, and I need to try and get a way of a summary sheet showing how many times "J.Bloggs" appears in All Sheets accorss the range C5:V5, but am finding it impossible.
I am trying to tally answers for a survey. Column A specifies one of 3 locations (Boulder, Larimer, Westminster) and column C specifies a grade for services between locations (Not Uniform, Slightly Uniform, Very Uniform). I was able to tally each separately using 'COUNTIF' and the conditions, but now I would like a total for each of the grades by location, i.e. a count of people answering both Boulder and Uniform, etc.
In my Sheet "List" I have list of persons working on different projects. I prepared graph after putting conditions on Project Type, Project Size, Project Year & Position (PM Project Manger). Every thing was done a in a nice manner with the help of below formula.
PJ TYPE , PJSIZE, PJYEAR, POSITION are ranges names.
But the problem was occured that in a year if a person work on small project more than once then he will be counted only once. But if he has worked in same year on Medium or Larage project then they will be counted separately. I tried to oversome the problem with the help of Pivot Table and put manually some legend P1, P2 & P3 against the person name if he is working on same type of project in same year. then count only P1 in my formula to count how many Project Manager worked on Project. like
Now i am trying that in a separate columm of # of PM there must be a formula which only put P or 1 for a person if he is working on same project in a year but i want that p or 1 only appear against his first entry i duplicate. for other persons it automatically enter 1 or p if they are appearing only once. i have tried a lot while using countif with multiple conditions but all in vain.
what I'm trying to do is to make a logbook for a machining center. Each part has an op10 and an op20, essentially front and back. And each part number falls into the category of OS or FS. I've used AND logic to make tables in hidden columns to be used by a countif statement to determine my totals. I.e. to determine if a scroll is completed, op20 has a a value of 1 AND column C is "OS".
I use =IF(AND(A9=1;C9="OS");1;" ") Then I countif criteria is 1 in the column i created with that statement.
That works just fine. Now what I want to do is to be able to create daily totals of OS and FS by simply modifying a variable date in a formula. So I'd like to essentially say: Countif Column C =OS and Corresponding column D = 1, and corresponding Shift date = 10.02.12(date to be variable). I'm at a wall here. Is there any way to do this somewhat simply?
I have 50 worksheets with shirt sizes. I am trying to count the number of instances that we have "XL".
So I use the formula: =COUNTIF(worksheet1!A1:worksheet50!A1, "XL")
I also tried: =COUNTIF(worksheet1!A1, "XL")+COUNTIF(Worksheet2!A1, "XL")+COUNTIF(worksheet3!A1, "XL")...etc
Niether work, in fact, excel decides just to leave the function as written above in the cell when I enter out of it or go to another cell. Sometimes it works if I "COUNTIF" multiple cells in one worksheet but will leave the formula as is if I try to manipilate it.
I'm trying to count multiple criteria from a second page in a work book, all the formulas i've looked up and tried do not seem to work... here's the formulas i've tried. DKOBULAR is the name of the 2nd page. D is the column used for the different resolves.
Countif: Is there a way to have a single criteria (a persons name) and multiple ranges example: a6:a10 c6:c10 and recieve the sum of that criteria and ranges?? I know there is I just cant get it.
I need to count a persons name entered in multiple ranges (cells or areas) on the same worksheet. I cannot make one big range because i will need to do the same for the b6:b10 but for a different "need" the a colum and c colum bieng "completed" the b colum bieng "not completed". I have tried =countif(a6:a10 + c6:c10, cell_with _persons_name) and for obvious reasons that wont work,
works however this workbook will be given to several people and the problem is that the sheet names will change. For example they will be working on several suppliers and will re-name Sheet 1, 2, 3 accordingly to the supplier name.
Is there any way that that i could still get this to work where the sheet names change?
In Excel 2003, I need a countif to check for 2 criteria: (1) the left function looking for the value "Territory" in column A and (2) value > 0 in column G. I only want to count the rows where both the criteria are met. I have tried different combinations of countif including "and" in the formula, but I cannot get it to work. What is the proper syntax?
=COUNTIF(F6:F200,"G") I get what I want. What I can't figure out is, I want to get the count of columns G, J, N, and R which has the criterior that I'm looking for, added together for a total.
And in reality, the rows do not go down that far, but they might, so I put 200 in there to be safe.
How to use COUNTIF when there are multiple criteria. For eg. I have 3 columns, and I want to count the # of employees in each row if all 3 criteria in columns C, D, and E hold true.
I'm after a formula (or a piece of quick code) to count how many times a value occurs in a range of sheets. I've tried COUNTIF, but it only seems to work for one sheet, not a range.
I need to create a formula which counts the number of times a username appears in column X based on a given value in Column Y. This data will not be static - will need to be refreshed regularly. Countif does not support multiple criteria - what is the best way to create this formula?
I'm trying to count the number of sheets that have the word, "Active" in the same cell on each sheet. Here's the formula I'm trying to use:
=COUNTIF(Sheet1:Sheet5!K5,"Active")
When I do this I get the value error.
I will have any number of sheets that say Inactive or Active and I want to have a "master sheet" that will tell me how many sheets say active and how many say inactive.
