Count Unique Records With Multiple Criteria With Formula
Sep 18, 2006
Count unique records in Column B where.
1.)
... Column H >=A1 and <=A2
2.)
... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2
3.)
... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2
4.)
... Column H <>"" and Column I >=A1 and <=A2 and Column J =""
A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
Column B - 6 digit number (or blank)
Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)
View 7 Replies
ADVERTISEMENT
Feb 14, 2008
As the title says, I need to count the number of unique records (names) in column A, where column L is = to something specific (X,Y,Z,W, whatever) for some statistics im trying to report.
Please see the attached spreadsheet. For role X statistics, I need a count of the unique names from col A, where col L = X. Based on my sample spreadsheet, the number should be 2. For Role Y, it should be 3.
There is the potential for spaces in the rows, and no one will be 'cross role'
I've got a few different methods to just count unique values with specific criteria in the same column, but I just can't make anything work for specific criteria in another column.
View 9 Replies
View Related
Apr 30, 2009
I need a formula that will:
Count unique records in column C
Where value in column N = "ABC"
And value in column I = "XYX"
View 9 Replies
View Related
Jul 7, 2009
For example, each job has a specific ref number and within that job ref number there is a number of lines of data, some of which we ignore and some of which we include. Each job will have a planning time using an old methodology (Planning A), a planning time using a new methodology (Planning B) and an actual time. There are a few other variables too such as the category of work.
I am trying to count the number of each unique ref no. within the sub category of work I am looking at (there are circa 50 sub categories of work). Each ref no. will only have 1 combination of work cat and sub category.
For example: ....
View 12 Replies
View Related
Oct 27, 2011
I have this formula to give me the number of unique locations and it works fine for cell J1:
=SUM(IF(FREQUENCY(MATCH($B$3:$B$22,$B$3:$B$22,0),MATCH($B$3:$B$22,$B$3:$B$22,0))>0,1))
What I am trying to figure out is how to populate cells N4:N5 and N8:N9 for unique locations by Fruit & Vegtables by Area A and Area B. I just have some basic data for this example as the spreadsheets are 30,000+ lines long.
View 2 Replies
View Related
May 17, 2012
I have two ranges of data and want to count the unique number of times the values in column A occur if they have two values in column B. For Example:
ColA ColB
333310143333306344441014444430635555161246666101466661612466663063
I want to count the number of times value in ColA has the value 1014 AND 3063. My intention is to create a table that cross references the two
10143063161241014331306333116124112
I searched and found answers where multi criteria were in multi columns but I can't seem to find a solution to this means of find the unique count in ColA.
View 3 Replies
View Related
Aug 12, 2009
A B C D
1 Yes Red Green
2 Yes Blue Black
1 No Blue Black
2 No Blue Green
2 Yes Red Black
I am interested in finding the number of unique entries under column A where column B = Yes; Column C = Red OR Column D = Black (answer is 2)
View 9 Replies
View Related
Oct 18, 2012
I am looking for a formula which will allow me to count unique values in a database, based on multiple criteria. Sample file attached (Formula required in cells J and K).
View 6 Replies
View Related
Jun 4, 2013
I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.
Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return
View 9 Replies
View Related
Jan 21, 2009
Is it possible to count the unique entries in a range based on the results of a filter that has been applied? I basically have a column with 2000+ cells that contain some matching values and I only want to count the unique entries. This will need to be a dynamic count as well as the filter criteria can and will change all the time.
View 14 Replies
View Related
Feb 19, 2010
I have a table in a sheet and I want a formula that will count the unique records. The simplified database is as follows (in a table):
View 6 Replies
View Related
Aug 22, 2006
I have a folder which has 200 files. I have extracted data from these files based on autofilter criteria. But there are many duplicate records extracted for the criteria. I need only unique records . Below are the codes. Where to I add the criteria for search records:
Sub ExampleSearch()
'Note: This example use the function LastRow
Dim basebook As Workbook
Dim mybook As Workbook
Dim rng As Range
Dim rnum As Long
Dim mnum As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
View 8 Replies
View Related
Sep 30, 2012
product1
product1
product2
product1
product2
product4
product1
product2
product1
product4
Need the simple formula to list only unique records of above data
unique list is
product1
product2
product3
View 3 Replies
View Related
Dec 7, 2013
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls
View 1 Replies
View Related
Sep 22, 2008
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
View 3 Replies
View Related
May 2, 2008
I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel.
I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.
What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:
BEFORE
HH Acct
1 1234
1 2345
1 3456
1 4567
2 9876
2 8765
3 1113
4 5556
4 4447
4 3335
AFTER
HH Acct1 Acct2 Acct3 Acct4 Acct5 Acct6 Acct7
1 1234 2345 3456 4567
2 9876 8765 8765
3 1113
4 5556 4447 3335
View 3 Replies
View Related
Jun 27, 2012
I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.
I need to know how many EE's are listed in column B if CR is on the same line in column A.
View 6 Replies
View Related
Jul 17, 2014
I have a ListObject table called Table1, and the column name is Table1[Company]. The records have been sorted A-Z for the Company.
The records look something like this
Date Employee Company
-------------------------------------------
1/1/11 Jane A Company
1/1/11 Jane B Company
3/1/11 Bob B Company
2/1/11 Bob B Company
4/1/11 Bob B Company
5/1/11 Bob C Company
What I need is a sub() to look in the column of Table1[Company] and determine:
The # of records that match the criteria given the Company name, and where they start and end. Ultimately, I'd like to copy and paste this portion of the table to another spreadsheet.
So for instance, if the Company = "B Company", then I could find out there are 4 records and there locations to copy and paste them.
View 1 Replies
View Related
Jul 5, 2014
I am setting up a template to use to track candidates throughout their lifecycle. I am also creating a 'summary report' on another worksheet that counts total number of candidates, counts the number of candidates that have been submitted to the client, count of the number of candidates that have been interviewed by the client, etc.
How do I count the number of candidates in the spreadsheet?
In Cells A6:B5, I have "Submitted"
In Cells A7-?? I have dates for those candidates that have been submitted.
Then a new section titled, "In Process"
A list of candidates are contained within that section.
Then a new section titled, "For Review"
etc.
There are multiple sections, so the date in Column A1 may not always be in a constant row, as it depends on how many candidates are within a certain 'phase'.
I have a column I5, titled "Status" This column has a built in drop-down list that contains pre-defined status, for example, "Submitted", "Candidate Declined", "2nd Message Sent", etc. I need to be able to create a summary report based on a count of candidates that fit certain 'Statuses'. For example, count the number of candidates that were Interviewed. This could be a combination of Statuses: Phone screen with client, 1st F2F, 2nd F2F, etc.
(At home, I am using Excel 2003. At work, I am using Excel 2007. So if it's easier to create in Excel 2007, that's fine.)
View 4 Replies
View Related
Aug 4, 2008
I have 1 workbook, with 3 sheets. Sheet1 (EVER) has 3000+ rows and 12 columns of customer information. This sheet is for all customers who have ever placed an order. Sheet2 (06-07) has 1500+ rows and 12 columns of customer information. This sheet has all customers who have placed an order in the last 2 years. Sheet3 has 1 row, which consists of the column titles (12 columns) that are on Sheet1 and Sheet2.
I need to put all customers that are on Sheet1, but not on Sheet2 in Sheet3. I have tried VLookup; advanced Filter and a number of codes in the last 3 days and have not been able to figure this out.
View 6 Replies
View Related
Jan 16, 2009
I have a spreadsheet with multiple columns and rows of data. I want to be able to type in a criteria and all the rows containing the criteria are called up. For example
Col A Col B Col C
Row 1 Apple Fruit 12
Row 2 Banana Fruit 15
Row 3 Carrot Veg 13
I want to have a cell on another sheet in which I can place a criteria, eg Fruit, and then the entire row 1 and 2 are displayed on the second spreadsheet.
View 9 Replies
View Related
Mar 28, 2008
i've tried to locate a matching post for my query, although a couple have been close I havn't been able to find one which matches what i'm trying to do. I have a spreadsheet which has two columns A:PersonIds and B:Month/Year. A person id can appear many times for a given month. What I am wanting to do is to perform a unique count of person id's by month. Take the following example:
ID , Month/Year
001, Jan-08
001, Jan-08
002, Jan-08
002, Feb-08
003, Feb-08
001, Mar-08
Below is what I expect the count to total
Totals
Jan-08 = 2
Feb-08 = 2
Mar-08 = 1
I've also attached an excel file with what i've just outlined. Is there a way to do this without using VBA, but with a combination of array's and nested if statements?
View 5 Replies
View Related
Mar 20, 2013
I have sales rep data with column headings as Sales Rep, Sales Date, Sales Item Code and Customer ID. There are only 2 sales reps in the data, I'll call them Rep A and Rep B. I need to extract rows where Rep A and Rep B have made sales on the same day to the same customer (it doesn't matter what type of sales). I don't want to extract sales by only one rep, it must be both reps on the same day to the same customer. I have tried a pivot table but it identifies all sales. Can this be done?
View 2 Replies
View Related
Dec 3, 2012
Count unique entries in a column according to two specified criteria.Specifically, I am trying to count the number of days in a month a supplier has a visited.Below is an example taken from the excel:
COLUMN A COLUMN B
Date Supplier
01-ago-12 Mr X
01-ago-12 Mr X
01-ago-12 Mr X
I want a formula that allows me to see how different dates Mr X has visited in each month. So in August he has visited 8 times BUT only on 5 different dates.
In the case of Mr Y I want the formula to give the answer 5 (even though he has visited 6 times in total, he has visited on only 5 different dates).
In the case of Mr X in September, the answer to the formula would be 2, since has visited on 2 different dates in September.
View 9 Replies
View Related
Oct 31, 2011
I have data in the following type
A Criteria B
A Criteria C Criteria A
C Criteria C
D Criteria 1
2 Criteria 3
Criteria 2
3 Criteria
I need a formula that will count the number of unique values in column A that have "Criteria" in column B. In this case the desired output would be 5 (A, C, D, 2, and 3), a blank cell in column A does not need to be counted.
View 9 Replies
View Related
Jul 18, 2014
Unique Count situation based on multiple criteria
Scenario: we have agents from different departments who produce different sales on different plan rates
Raw Data
Employee
Plan Rate
Payout pts
[Code]....
View 9 Replies
View Related
Feb 19, 2009
I'm facing a big problem and I can't found a solution for days.
I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
Let me Put to you an example for you been able to understand my problem.
Example considering Range "C2:C20" exluding blank cells
(1) - To count unique values
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
(2) - To count unique values based on criteria in range "B2:B20"
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))
(3) - To sum unique values on "E2:E20" based on same criteria
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))
They all work fine. But I need to insert a merge at Row 10 for
better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.
I just don't know what to do guys ....
View 9 Replies
View Related
Jan 28, 2009
I have a list, 50000 rows long with phone numbers and the service provider it belongs to. I want to extract some of this information. I'm looking for a formula which can give me the following: How many unique phone numbers is there in column B from service provider number 4 and 5? The following pic is just an example of how the list is compiled. How can I get sum up only the numbers from 4 and 5 and not the whole bunch? If I use this formula
Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
=SUM(N(FREQUENCY(D1:D51112,D1:D51112)>0)). I sum up all the unique phone numbers, but I don't know how to set a "if service provider is 4 or 5" if you get what I mean. This was the first part. The second part is finding out how many times each unique number from service provider 4 and 5 is listed.
View 4 Replies
View Related
Dec 3, 2007
I need to count the amount of Unique codes in column A BUT ONLY if they are still OPEN status. Hopefully the attached example will be explaination enough (I want to automate the RED figure...
View 3 Replies
View Related
Feb 17, 2008
My data is like:
A1: 5 -- B1: X
A2: 5 -- B2: X
A3: 5 -- B3: Y
A4: 3 -- B4: Y
A5: 3 -- B5: X
A6: 8 -- B6: Y
A7: 2 -- B7: X
A8: 3 -- B8: Y
A9: 1 -- B9: X
I want the count of unique numbers in column A based on the criteria i have on column B.
So the count for X should be 4 and for Y should be 3..
Can i achieve this using formula only?
View 5 Replies
View Related