I am using the following formula to count the total number of contract types if 'ITD $K' equals '0' zero. But it returns 0 as output.
=SUMPRODUCT('PROCUREMENT CONTRACTS'!$E$5:$E$225="GROSS")*('PROCUREMENT CONTRACTS'!$J$5:$J$225=0)
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 use a countif and formula but can't get it to work. This part of the formula works fine =COUNTIF(J:J,"
View 9 Replies View RelatedI have a column of numbers ranging from 0000-9999. They are not in order and multiple numbers are used more than once. I would like to know how many times the numbers in the range of 4000-4999 appear. I am trying to use the formula =COUNTIF(F4:F500,"=4????") or =COUNTIF(F4:F500,"=4*") but neither are working. If I do a single number like this =COUNTIF(F4:F500,"=41420"), it will count them for me.
View 9 Replies View Relatedcreating a multiple if statement. I have 3 columns in Sheet 1 called "Raw Data". Column A is dates, Column B is Clients and Column C is Products. I want to count the number of products sold to a specific client on a specific date. In Column A, the are multiple dates repeated as there are different sales reps so i have the dates repeated several time.
the current formula i am using is:
=COUNTIFS('Raw Data'!$A:$A="2013/01/01",'Raw Data'!$B:$B,="MotorCity",'Raw Data'!$C:$C,="Cars")
the problem is that i keep getting 0 as a value. If i remove the Date as a variable, the formula works. I just cant seem to count multiple criterias if the date is one of them.
I have a drop down list called Status including Testable and Not Testable
A1 = Testable
A2 = Testable
A3 = Not Testable
A4 = =COUNTIF(A1:A3,"Testable")
Gives me 0... i don't get it. Should show 2 no?
Ok so I am trying to streamline the Shirt ordering process at my work. I have made a table with Employee names as Column A, then Row 1 is shirt sizes from small to XXXL. The Cell values are going to be the colors people want. I want to total the number of each size/color combination. But someone may want more than one of the same color. For instance you would enter "BK, BK" if you wanted 2 black shirts of that size. This is where the issue with COUNTIF comes in. If a cell has the same value twice, it only counts it as one because counts the cells with an instance of a value, not the actual count of instances. Is there a way to make COUNTIF or a better function for this?
View 6 Replies View RelatedI'm using Excel to schedule employees shifts at my restaurant. Column A is their name, Column B is their start time, Column C is their end time. I'd like to be able to count the number of employees working during any given hour of the day to assure I've got enough staffing each hour. I'm thinking it will require a COUNTIF function but I can't figure out how to make it work. I've attached a sample sheet.
View 4 Replies View RelatedIn excel 2010, I have 2 spreadsheets, the first "Active Total Master Turn" has the data, the second, "Pending" has a reporting chart. A cell on "Pending" contains the following formula:
=COUNTIFS('Active Total Master Turn'!F:F,"RK*",'Active Total Master Turn'!H:H,"Painting",'Active Total Master Turn'!C:C,"
I am trying to track metrics for a QC dept. Right now my sheet has 35 lines of data and a header row, for 36 lines total. This is my funtion that isn't quite right:
View 2 Replies View RelatedI have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.
For instance:
If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.
Sam Douglas:BI
Sam Douglas:BI
Sam Douglas:SI
Sam Douglas:BI
BI = 3
SI = 1
I have following data (two columns Parent and Child), now I want to apply Countif on Child cell.
But in Countif I want to provide the criteria...let say only count those childs whoes parent is A.
How to do this in Excel.
Parent Child
A e
A f
B g
B h
B i
C j
C k
I'm reasonably new to Excel, and have a fairly basic question to check out:
I have been using the COUNTIF function to count up numbers of items in various categories in a column.
The formulae I have been using are like this:
=COUNTIF(F$3:F$201, "Red")
or where I've wanted to combine various comments
=SUM(COUNTIF(F$3:F$201,"Yellow")+COUNTIF(F$3:F$201,"Cream"))
I'm not sure what formulae to use to count up
1) the total number of entries in that column, so that I can make sure that I haven't missed some (without having to check manually!)
2) how to count up the values that do not match the other categories that I have specified in the COUNTIFs: this would be a value for finding how many 'other' entries there are in that column, without having to specify those values
I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.
What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4
I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")
important columns are: "D", "G", "L".
this is about a project is a granite to be installed in 104 units, these units are divided in 4 types (column "L") K1, K2, K3, K4
I tried IF, SUMIF, COUNTIF, ETC but I think that I need is some combined function that I'm not able to figure out.
as you can see in the attached file, my units 101, 102, etc is not been installed yet (column H is empty), but the units 209, 210, 211, 214 yes was installed on 5/6 for example.
for example, if i use =COUNTIF(H4:H20,">0"), will return me all the H that is not empty (=3), how many kitchen was installed so far, but, I would like to know how many of these returned units (3), is my different types: K1, K2, K3 and K4....
I am trying to count the status and type of some work so:
Column A would contain the status of the work e.g. open, in progress, closed etc.
Column B would contain the department: ict, development, operations, etc.
I want to do a summary that shows: How many are in ICT are open, closed etc.
I can do a countif to get the total open, in progress etc or total number of ICT jobs but not ICT In progress.
What i want to do is count but only if another cell meets a certain criteria,,
Let me explain better,,what i want to do is count the number of 'Completed' cases by each individual, i.e. count if the cell A1 = Completed and B1 = Kevin and the same for 'Pete'
I'm sure i need some sort of nested =IF AND formula but just can't think how the nesting should look.
B
CompletedKevin
CompletedPete
CompletedPete
Parked Kevin
SubmittedKevin
CompletedKevin
Started John
With AdviserPete
With AdviserPete
Started John
Started John
I have attached a copy of my spreadsheet .My formula is in column BA and I am trying to figure out which students have 5 A* - C. We are only allowed to add 2 vocational subjects to this formula so basically 3 (or More) GCSEs + 2 BTEC/OCR Nationals. Now my formula works pretty well except if the candidate has 4 GCSEs and 0 or 1 BTEC it is still calculating as though the candidate has achieved 5 A* - C.
I have highlighted GCSE Subj in pink and Vocational Subj in Puple.
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.
- Column D includes dates.
- Column E includes text, either "Yes" or "No".
I want Excel to count all cells in Column D between 1-Jan-2009 to 31-Jan-2009 + any cells that say "Yes" from Column E on the same row.
I'm trying to count cells in one column that match a variable only if it also matches a variable in another column. For example, I want to count all of the cells in column A that match "Franklin" only if column D shows "True".
View 5 Replies View RelatedI have a formula =IF(AND(F14=3,G14<2)=TRUE,1,"") which is in cell J14. I also want to 'add' to this formula an 'or statement' (I am not sure of the terminology, so taking a shot in the dark). So it would continue to complete the calculation as normal, but if there is a '1' in K14 it would also output in J14 as a 1
View 2 Replies View RelatedI have a worksheet where I am trying to count the number of occurences of several text strings.
For example:
I'm trying to count how many times "paid in full" and "fully paid" occur in column A.
I have two formulas, and both seem to work, but since I don't really understand either of them, I'm wondering which I should use and how I would adapt it to include additional text strings. (Like adding "paid" to the list)
Here are my formuals (I didn't write either of them, another co-worker did)
=(COUNTIF(A:A,"paid in full"))+(COUNTIF(A:A,"fully paid"))
=SUMPRODUCT(--(A1:A50={"paid in full","fully paid"}))
Also, if there is another and easier way to do what I'm trying to do, I'd love to know.
I have a table with 2 columns - Gender & Age. Now, suppose I want to know the number of Males in the table with age greater than 14, how do I do that? Can a Countif formula be used for more than one condition or is there some other formula?
View 4 Replies View RelatedI have two different worksheets.
One has dates recorded as dd-mmm-yyyy (e.g., 28-Nov-2009).
On the other, I have to create a lot (over 1000) of countifs against the months/years of these dates. So, for example, if there are several dates on the first workbook that fall in Nov-09, i want to do a countif on the second workbook that tells me how many Nov-09 dates there are.
My boss has asked me to work this one out and is putting me under great pressure to resolve it. I have tried Vlookup and COUNTIF etc but just cannot get to make it work. I have attached an example file but to explain:
I have a database of inspectors faults, in column B, I have the week number, column*C, I have the part number & column C the fault category (this includes OK). The actual database is up to 5000 rows now.
What I need to be able to do is in one cell (column H2) count all the OK's for week 1 and all the Not OK's (column I2) in week 1 and the same for week 2 (column H3) and week 3 (column H4) and so on for the whole year. I have tried a vlookup using cell G2 (containing "1") as the search and the week number column as the range and then counting if OK, but it never worked!
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 2 ranges with values, and I want to use countif to see of there's a same value in each of these ranges.