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.
View 5 RepliesWhen using the following:
=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.
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.
A B
1 1.00
2 Yes 4.78
3
4 5.00
5 Yes 4.89
6 11.99
So this example would count 3
I've been asked to extend the counting capabilities of my interface to pick up the profession of each person when they answer whatever is selected in the 'Interface' worksheet (cell B2) put them in my interface.
I've been trying various incarnations of SUMPRODUCT along with what DonkeyOte helped me with before, but currently no joy. Hope that makes sense - have a look at previous post for further details. I've attached an example to look at.
I have 16 columns with 10 rows with different single digits in them. I want to count the number of times the number 2 appears in columns A, C, E, G, I, K, M, O (in other words every other column in this case).
I know how to write the formula by using countif to find the results but it is rather long. The fomrula would look like this:
I have two columns and have to count all "DATA PAPA" rows
How i can do it?
I am using this formula
=COUNTIF($C$1:$C$4000,B1)
How can I add a third column D to the formula to check if the value in 'D'
is the same
eg.
A B C D
1-Jan 1-Jan 4523
2-Jan 4523
3-Jan 4523
4-Jan 4501
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 have a spreadsheet where I need to count the # of times the value 1 is in a row, but the columns that I need to check is every other column.
View 3 Replies View RelatedI need to have cell with the total rows of Column E that equals TODAY() AND Column F = "Repair". I have tried various COUNTIFs with not much luck.
View 5 Replies View RelatedI have three seperate Formulas on a summary page that will count dates depending on whether they will be approaching in the next month, whether todays date falls within them and if they are expired or are less than todays date.
Basically the form was developed to monitor a maintanence schedule and give a summary to the person in charge of cleaning so they can forecast there work load. I have managed to get the formula working for "whether todays date falls within them" ("# of Computers than can be cleaned today" as it appears on the summary form) but I cannot seem to get the other formulas figured out.. Well at least the formula that checks to see which computers are past due.
As far as the formula that checks to see which computers will need to be cleaned within the next month, I don't know how to make that work at all considering the setup of my Date input page.
counting cells if blank. I have two columns E and J . For example if E5 and J5 are both blank I want that to count as 1.
I have two columns A and B. Column A contains 'Machine Nr' and column B contain 'Week Nr'. In cell C1 i want to write a function so that it counts the number of weeks in Column B where week number is 4 AND the machine nr is 1 in column nr A.
Column A Column B
Machine Nr Week Nr
1 4
1 4
2 4
2 4
3 4
3 4
I would like to use the sumproduct or countif formula for a set of data. I have looked around the forum and have not found what I need so
Column A______Column B
West___________1
West___________2
West___________1
West___________2
South__________ 1
South__________2
South__________1
South__________2
I want to count column B for all "West" (column A) and I don't want duplicates. So it would count two unique characters for West and two unique characters for South. I want "west" and "south" separated.
Having trouble getting Excel to compute a value based on two columns on another sheet. What I need it to do is see if column A has the value I specify, then look in column B and see if that date figure is greater than 180 days, and then count it. So I might have 45 that meet criteria A, but then need to find items in that subset that meet criteria B...
View 9 Replies View RelatedI have a formula (below) where I want it to count the cells that contain a date that is less than or equal to today, but only when the adjacent cell is blank. The latter part of the formula works, but I'm struggling to tell it to only do the count when cells in column I are blank;
=IF('QS12'!I10:I3120="",COUNTIF('QS12'!H10:H3120,">="&TODAY()))
Following on from: http://www.excelforum.com/excel-misc...s-formula.html
I used the above formula to work out how many days a call went out of our SLA, that works great (puts the values into K)
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 table like this:
Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...
I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).
It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).
I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))
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.
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.
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"))
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
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"),--(PMCOUNT="P1"))
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 use Countif() when 3 ceritera are true
ceritera
--------
1) Branch Name
2) Status
3) Login Date
can this be done by any mean
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(DKOBULAR!D:D="resolveA")+COUNTIF(DKOBULAR!D:D="resolveB")+COUNTIF(DKOBULAR!D:D="resolveC")+ COUNTIF(DKOBULAR!D:D="resolveD")
=COUNT(IF(DKOBULAR!D:D="resolveA",IF(DKOBULAR!D:D="resolveB",IF(DKOBULAR!D:D="resolveC",IF(DKOBULAR! D:D="resolveD")))))
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:
=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9????")
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,
I currently have a workbook with multiple sheets and would like to use countif in order to get the information I need.
this formula:
=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3,4,5}&"!C3"),"T4"))
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?
Is it possible to do a countif formula using a range of sheets.
i.e. I have info on 6 sheets (same layout on each) and I want to do a countif "Internet" that's in column G in all the sheets.
Is it possible to count them all together?
looking to use =countif for an address file and want to filter out duplicates of last then first name.
col F has last name
col d with first name
I've tried using the Conditional Formatting funtion, but am not understanding how to incorporate multiple columns,