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.
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 am trying to create a COUNTIF formula which will work with two conditions. If you see the attached spreadsheet you will find the data that I am trying to apply a formula to. I have my data in the table on the right. The table on the left is supposed to show the number which the number of destinations that had a certain range of visitors.
As you will see that there are 3 destinations that had 12 or more million visitors, this was counted using a basic COUNT IF formula but for the rest of my data how can I apply the formula so that the correct number of destinations are counted. For example what formula would be needed to count the number of destinations that have had 8-11.9 million visitors. I am guessing that the formula will have the conditions ">=8" and "<=11.9"?
I have coded data for age range in one column and for answering yes or no to a question in a different column. I need a formula that will count the number of yes answers for each coded age range.
a= 14-15 years old, b=16-17, ect.
Column One Column Two age(coded) Yes or No a Y a N b N c Y a Y b N
I need to be able to count the number of yeses for each coded age. If the formula works it should give me the following output given the sample data above.
14-15 year olds output with the data above would be 2
16-17 year olds output would be 0.
How would I write this formula? I tried countif and sumproduct (though admittedly sumproduct I don't know at all and just tried to find a similar situation and tried it with my spreadsheet).
I just need it to be able to test two seperate conditions on two seperate columns but i cant work out how its done
for example, i have two columns on a sheet, one called type and one area, both are text, i need to count how many items of one type are in a particular area. so if i have two columns "area" and "type" i want a total count of how many rows contain area="north" and type="b"
I've come across this page around simulating a countif with two conditions. Instead of two 'equal to' statements though, one is a 'less than' statement, and something does not seem to be right - I am not sure if I am getting the syntax wrong but am currently just getting #VALUE! across all cells I try and use.
I am using =COUNTIF formulas to calculate different conditions. such as, calculating how much of one product do we have and how much is is received under 30 days. i want to combine those formulas in one cell to calculate two conditions in different columns.
1-So first i found out the date difference. I used =DATEDIF(Sheet1!C10,Sheet1!E10,"d")
2-Then I found out how many of one type do we carry, using the =COUNTIF. =COUNTIF(Sheet1!J2:J3810,"DAZ")
3-That was easy, But now i have to sepereate them into 30 or less, 60 days or less, 90 days or less. Then i used =COUNTIF(D2:D3810,"
the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.
Column: A B C D E Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)
What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.
Criteria 1 If any of the coulmn data contains a value less than .5 I need a 1 placed in column D
Criteria 2 If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page. =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula: 1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
D E I J Account1 Sum1 Account2 Sum2 151 93,79 BF2479 30,00 1476757 93,79 BF7 22,00 BF2479 30,00 151 93,79 86041 84,03 23235 30,00 534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00 534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1' If DL2=3 and CK2=2, 2 If DL2=1 and CK2=2, 3 If DL2=2 and CK2=2, 4 If DL2=1 and CK2=1, 5 If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
X Y Z DOU Quarter Sum 0 Q1 = sum of 1's in Q1 1 Q1 = sum of 1's in Q2 1 Q2 = sum of 1's in Q3 0 Q2 = sum of 1's in Q4 1 Q3 1 Q1 1 Q4 0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
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.
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.
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.
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?
