I have a column of data that contains a two letter identifier, which can sometimes be combined with another 2 letter indentifier: Example
Column D
AA
DC
DC,AA
How would I get countifs to count each occurrence of AA or DC by itself?
I have two sheets:
Sheet 1
Col A
A
B
C
D
Sheet 2
Col ACol B
AXX
BYY
AXX
AZ
DD
DD
I want to count the number of unique occurance of value in Col B in Sheet 2 as per value in Col A in sheet 1 and Sheet2.
My output result should be like following:
Result
Col ACount of col B value in Sheet 2
A2
B1
C 0
D1
I have to calculate bonus payments for people working O/S. I have a 5 year calendar in month blocks (60 months/columns). In each month there is a percentage 'time O/S' figure.
If an employee is O/S for 3 consecutive months they get bonus 'A', 6 consective months bonus 'B', etc. How can I evaluate the 60 columns, returning the number of times 100% occurs in 3 consecutive months/columns. I have found examples of similar solutions but they will return a value of 3 if there are 5 consecutive months of 100%...
ie. 100 100 100 100 100
= 100 100 100 *** ***
& *** 100 100 100 ***
& *** *** 100 100 100
= 3
but I need it to equal 1
In one column, i get the ID. In the next column of the same row i get the status. My question is to find the number of occurrence of a specific ID with a specific status on another sheet. Attached worksheet sheet "count" has the source with ID at column C and status at column D. My expected result is in another sheet "expected result". I think a macro is needed, but i can't really figured out the way to do this counting.
View 5 Replies View RelatedColumn A Column B
2012 20
2012 30
2013 50
2013 30
2013 50
2014 10
I've got two columns like this. I need to see if a countifs() would handle it for me, or if there's another formula to use.
I need to get a count of all fields in column B that are unique and are 2013 in Column A. In this case the answer would be 2 (in 2013 there was a 30 and a 50, duplicates are skipped).
I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".
View 1 Replies View RelatedCountif can be used to count the no. of occurrence of a certain value within a range. However, if the range is being filtered, can the no. of occurrence be counted?
View 3 Replies View RelatedWill it be possible to Count continuous occurrence of each signs 1-X-2 and arrange them in corresponding column From Q To AE as shown below in example table: 1
Draw result is shown in cells A2:N11 and result of Count continuous occurrence of each signs 1-X-2 is shown in cells Q2:AE11
For example we take a look row 7 where first sign-2 has occurred 3 times, as
Column Q is for to place result of sign1
And column R is for sign-X
So far count of sign-2, which is 3 times, must be paced in cell S7,
And then count of sign-1, which is 1 time, must be placed in cell T7,
And then count of sign-2, which is 5 times, must be placed in cell V7,
And finally count of sign-X, which is, also 5 times, must be placed in cell X7,
[code]....
Will it be possible to Count continuous occurrence of each signs 1-X-2 and arrange them in corresponding column From Q To AE as shown below in example table: 1
Draw result is shown in cells A2:N11 and result of Count continuous occurrence of each signs 1-X-2 is shown in cells Q2:AE11
For example we take a look row 7 where first sign-2 has occurred 3 times, as
Column Q is for to place result of sign1
And column R is for sign-X
So far count of sign-2, which is 3 times, must be paced in cell S7,
And then count of sign-1, which is 1 time, must be placed in cell T7,
And then count of sign-2, which is 5 times, must be placed in cell V7,
And finally count of sign-X, which is, also 5 times, must be placed in cell X7,
Example table: 1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1P1P2P3P4P5P6P7P8P9P10P11P12P13P14
1X21X21X21X21X221X21X21X21X21X
11111111111111
3111XX22X11111X
322
1
51
[code].....
count occurence of name Vijay in single cell b4. excel formula.
Vijay is good, Vijay lives in Delhi. My another friend with same name Vijay….. Vijay joined exl in 2008. Vijay has one sister named Vijaya.
How To Count And Fill Occurrence in sheet2 ...
View 9 Replies View RelatedI have a list of names (300 now but growing every month by 100-200). In the row with the name contains data I need to view: date, $, #, etc...
I want to quickly see whos name appears the most, 2nd most, 3rd most, etc.....(at the end of the year I will have 2000+ names, most names will only be listed 1x, I suspect nobodies name will be listed more than 60x.)
I also need the ability to view the relative data of the person's name that is listed most, 2nd most, etc....
I need to identify every 5th occurence of a product code (In column B)
the list won't be consistent i.e. Code 80100811 may appear in row 2, 17, 35, 47, 51
So I would need to identify the one in Row 51 in the above example,
WorksOrderNumber
Product Code
DateEntered
Column1
56
80100811
01/08/2012 00:00
[code]......
I have a spreadsheet that lists all the work done by employees within a specific area. Some employees cover multiple areas.
I am now needing to work out the average work completed by each area. I need a formula that will count the number of employees by each work area.
I know this is something I have done before, but my mind has gone blank and I can't for the life of me work it out again (it's one of those days).
Row 3 of the spreadsheet contains the codes for the work areas they cover (CM, V & TC) and some employees only cover one (which would be a simple CountIf) but some have multiple.
What is the formula to, for example, count the number of people who have CM in row 3 even if they also have other entries in that cell.
I have a spreadsheet with two colums with dates. The names of the ranges are 'DataContractualStartDate' and 'DataContractualEndDate'.
How do I manage to count the no. of records with equal dates at the same row?
I have used the following but this gives me zero, while in the example below the result should be 3.
=COUNTIFS(DataContractualStartDate,DataContractualEndDate)
The table looks as follows:
dContractualStartDate
dContractualEndDate
01-05-2012
01-05-2012
[Code] ....
In an employee attendance file I am trying to count the number of times an employee has taken 3 or more days of leave together (continuously) in a month. My attendance file looks something like this
sat
sun
mon
tue
wed
thus
fri
[Code] ..........
In the example above E001 has taken 3 continuous days leave twice so formula should return 2, for E002 & E003 the answer would be 1 each.
I know there's a pretty compley formula out there that counts the occurence of say Fridays in 2009 - does anyone have this? I had it before in a file but ranged valued the results showing the count of each day of the week for each month in a given year.
View 9 Replies View RelatedI'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
I am using the formula to count if c- is in the cells: =COUNTIF($B2:$U2,"C-")
However, I need to change the formula so rather than just count if C- is in the cells, I need it to count if A*, A+,A, A-, or B+ are in the cell instead.
I would like to lookup the last occurence of a particular cell value in a worksheet and use the cell value in the column immediately adjacent to this one in a calculation.
For example, The text "Run #" occurs several times in a sheet with a numerical value in the next column over. I would like to use the last occurence - or as far as that goes, the largest occurence of the numerical value. Unfortunately, there is additional data in between the "Run #" occurences, so a simple max() for the column will not work.
I am trying to extract the last occurence of ppp (4th column). So as long as 4th column is "ppp", i would want to extract the row wher the the last "ppp" occurence start and display in sheet2 "108-Jan-07vcdppp4". If data in 4th column is detected to be "ppp1", it would also extract the row wher the last "ppp1" occurence start and display in sheet2.
Note that the number of ocurrence is random and do not follow specific pattern. Pls see the attached for the example.
I'm currently working with the formula
=COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000")
However I need the formula to count only one for the n possible duplicates in the Data[Name] column.
Say I have a list of part numbers, and each part number has an X or a 0 next to it, depending on my own set parameter.
How do I then report that data on another tab so that it counts how many there are in a set area AND if its an X.
At the moment I have this:
I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.
Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?
I'm trying to do a COUNTIFS for a set of data. What I want it to do is pull everything that is assigned to a certain person that has no date in one column, and information in another. For example:
Name
Date
Comment
Michelle
4/30/14
Closed
Michelle
Issue
Michelle
4/30/14
Closed
Michelle
4/30/14
Closed
Michelle
Issue
So based on this dataset, I would get the number 2 for Michelle (row 2 and 5 have no date in column B and a comment in column C). I've tried a few variations and can't seem to make it work right.
I am using the COUNTIFS function. I can us it with simple criterias but unsure to go about this criteria.
I would only like to count the cells if the range in question is equal or greater than S3 but is equal or less than T3.
I don't know how to use the => signs.
I have a list of information regarding jobs that need to be carried out by a number of companies by a number of dates. What I want to count is the number of jobs that company A need to caary out by a specific date and then the number that company B need to carry out by the same date. The formula basically needs to say countif column a equals company A and column C equals 11/11/09. I have tried numerous combinations of IF, Countif, SUMif, And etc but can't get the formula to work,
View 9 Replies View RelatedI'm looking to count the following:
A B
a 1
a 1
b 0
b 1
c 0
a 1
If column A=a AND column B=1, add column B on the same axis. I can only get a function that adds all B column if A=a. The function should equal 3. Cells B1, B2 and B6 should be the only ones counted.
I need to find out last occurrence of price (Last Date of Occurrence). Column A contains Date; Column B contains Highest Price of Equity Stock in that date. Reference price (Price we want to look up) is calculated in Cell E1, Cell E2 contains start of evaluation period (date), Cell E3 contains end of evaluation period (date). I want to find out the date on which "last" occurrence of Reference price in Column B "during the evaluation period" was found.
I have tried using array formula
{=MATCH(E1,IF(A2:A21>=E2,IF(A2:A21=E2,IF(A2:A21
I am trying to find a way to return an second answer for a second occurance using a VLOOKUP function. (maybe it can be done with something else?)
ex. range= A1:C3,
A1 = 40CA B1 = 30R2 C1 = TRUCK
A2 = 40CB B2 = 30A1 C2 = CAR
A3 = 40CA C2 = 30B3 C3 = TRAILER
VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"
How can I get a formula like this to return "TRAILER" the
second occurance of "40CA"?