Count Unique Entries In One Column That Meet Conditions
I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]
The situation:
I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.
namegender
jones m
martinf
smithf
collinsf
wilsonm
jones m
martinf
hughesm
wilsonm
martinm
smithf
west f
jones m
west f
martinm
The challenge:
In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!
The correct result: ...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Counting Unique Entries That Meet Certain Criteria
Every other line is the planned production and the others are filled in after as the actual production I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day. Here is my problem- if a station is running two different products it gets counted twice. I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines. Attached is an example of what I have with the formula and below that is what I am looking for
View Replies!
View Related
Count Number Of Cells That Meet Specific Conditions - Error Messages
I have a spreadsheet which is linked to several other worksheets. I have managed to include formulas to count how many cells have numbers between 101 and 5000 by using this formula - =sum((h2:h500>=101)*(h2:h500<=5000)) but now I want to count the number of cells in another worksheet that are equal to or less than zero. When I use the same formula as above it counts all the blank cells. I have tried using a countblank formula and then deducting this from the result, but unless the other worksheet is open the countblank formula does not work.
View Replies!
View Related
Count By Unique Entries
I am hoping this can be done with formulae. Starting at C7 and continuing down the C column there is a list of names which could potentially run from C7 to C5000. This list of names will contain duplicates. For each name there is a corresponding 'reason' in the F column which will contain the word 'Truancy' or 'Late'. I need a formula that can count the number of UNIQUE names in the C column which correspond to the word 'Truancy' or 'Late' in the E column. An example, [Name].....................[Reason] C7 ...........................E7 John Potts.................Truancy 2 Matt Jones................Truancy 10 John Potts.................Truancy 4 Matt Jones ...............Late AM Pete Burns................Late PM Pete Burns ...............Late Both Steve Lopez..............Truancy 6 Count of unique names with the word Truancy in the corresponding E column = 3 [John Potts has 2 instances of the word truancy in column E but this is only counted once] Count of unique name with the Word Late in the corresponding E column = 2 [Pete Burns has 2 instances of the word late in the E column but this is only counted once]. I have also included a sample workbook.
View Replies!
View Related
Count Unique Entries Within Variable Date Range
Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why. Goal: create a count of unique entries within a defined variable date range I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells. I've been attempting to use the DCOUNT function but I'm not getting the correct result. Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect. It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
View Replies!
View Related
Count Unique Entries Within Variable Date Range ..
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
View Replies!
View Related
Count Number Of Rows With Unique Entries In 2 Columns
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C. I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row). I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column. All the cust numbers are unique so would I be able to use a wildcard?
View Replies!
View Related
Count Where Text Value In 1 Column & Month Of Dates Meet Condition/Criteria
need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)). So now I just need it broken down by training type per month. How many x's in each column for February as an example.
View Replies!
View Related
Filtering For Unique Entries Based On One Column
This may be a basic question but I haven't been able to find the solution: I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example 0001 a 0001 b 0002 c 0002 d 0003 e 0003 f I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example: 0001 a 0002 c 0003 f I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
View Replies!
View Related
Advance Filter Check Unique Column Entries
How do you remove entries from a column such that only unique entires are left? Say the spreadsheet contains only a single column: david kris tony hayley kristy john david test hello david I tried an "advanced filter" with "unique records only" checked. It gets rid of one david, but not 2 (there are originally 3 of them, and we want to leave 1) This is the result. david kris tony hayley kristy john david test hello
View Replies!
View Related
Count Data In Column X Based On Conditions In Column Y
I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2". In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.
View Replies!
View Related
Count Unique Logs With Multiple Conditions Of Multiple Sheets
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. SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View Replies!
View Related
Advanced Filters - Having Multiple Filters And Conditions (Unique Count)
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View Replies!
View Related
Count Unique Items In A Column
Looking for a formula that will give me a count of unique entries (i.e. duplicate entries to not be counted.) basically a formula that says look in D4:D4001 and count only one of each entry....do not count the duplicate entries.
View Replies!
View Related
Count Unique Records With Criteria From Another Column
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 Replies!
View Related
Count The Number Of Unique Numbers In A Column?
I have a column where I have record numbers. The record number can end up duplicate in some cells. PROBLEM_ID IBM-02204732 IBM-03252238 IBM-03252238 IBM-03335648 IBM-03534918 IBM-03534918 IBM-03862015 IBM-03862015 IBM-12737629 IBM-12737629 IBM-17269762 I would like the get a formula that can count the number of unique numbers in that column? I would like to get if possible to get a formula that summarize the number into a single cell.
View Replies!
View Related
Count The Number Of Unique Numbers In A Column
i'm currently trying to count the number of unique numbers in a Column using this formula: =SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&"")) So far so good.....But I want to add some conditions like only count the numbers that meet the condition say N1:N3000="SE*" =SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&"")))) On this one I get #VALUE! and i can't figure out why. I have also tried to put the AND/OR condition before the actual starting of the array of the SUMPRODUCT but nothing.
View Replies!
View Related
Count Unique Values In A Filtered Column
I need to count the number of unique names from a column of filtered text. On the attached example D5 is where I need the value, the current array I have does not work when the filter is applied by changing the product in drop box. I need to have a count of the unique account names from column A.
View Replies!
View Related
Unique List Would Grab All Unique Entries
I have a data table that repeats as follows: CITI SCB SCB SCB SCB SCB SCB SCB RBC RBC RBC RBC From the data above I need to make a new unqie list that would grab all unique entries. CITI SCB RBC I am trying to use the following guide: http://blogs.techrepublic.com.com/howdoi/?p=111 Get stuck on "Listing A:
View Replies!
View Related
Count Unique Words Appearing In A Column Once Per Row
I would like to modify the below macro so it only counts a word once even if it appears more than once in a row. The reason I want to do this is so I can get a snapshot of the data without certain terms being over-represented. For example, in the attached example the word 'Microsoft' appears 5 times in row 4 but I only want this to be counted once. In the whole data set Microsoft appears 20 times but only in 7 of the 20 rows so I would like the count to be 7. The example is set out as follows: In column B there is a description field which in practice will contain consumer complaints and inquiries. To keep the data anonymous the description is filled out with random words and all other columns are blank. The output of Andy Pope's unique word counting macro appears in I:J.
View Replies!
View Related
Sumproduct (count The Number Of Unique, Or Distinct, Company Names In Column)
I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count. I should also point out that most company names appear multiple times in column [C7]. a) Count the unique company names in column [C7]... b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.
View Replies!
View Related
Summary Count That Meet Criteria
In my spreadsheet I have several columns and I have written a formula that has two conditions. If these are met the result is counted. However I want to add another condition. I need to write a formula that displays information relating to the conditions in a cell if the other two conditions are met. For Example, if the formula picks out that a row has the word 'John' and has the a number between 1 and 14 it will copy the inputted data at the beginning of the row.
View Replies!
View Related
How Do I Count Nonblank Cells That Meet Criteria In Another Cell?
I need to add nonblank cells (cells have text) in a column that equal the criteria of another cell. So I want to add the cells in Column C that have text but also equal the date in Column A (which is equal to the date in Cell A1). I have tried the follwoing: =SUM(IF(A2:A19=A1,IF(C3:C19="x",1,0))) =COUNT(IF((A2:A19=A1),C2:C19)) =IF((A2:A19)=A1,COUNTIF(C2:C19,"x"))
View Replies!
View Related
Highlight Row If Cells In Row Meet Multiple Conditions
I have a spreadsheet of actions/tasks. I have a column with the expected completion dates of each action and another column stating whether the action/task is "open" or "closed". I would like to highlight any actions that have gone past their expected completion date and are marked as still being "open". Obviously i dont want any row that are closed to be highlighted. I know the =TODAY()- B1>0 formula will highlight the dates cell that have expired but how do i extend this formula to what i require.
View Replies!
View Related
Count Formula: Count Total Entries In Columns
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View Replies!
View Related
Vlookup With Conditions To Find Multiple Entries
I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008. A B C D 999 1/1/2008 1/15/2008 $10 999 1/15/2008 1/31/2008 $20 998 2/1/2008 - 2/25/2008 $15 I have another table (table2) in another sheet in the same workbook have a material and date. A B C 999 1/10/2008 999 1/20/2008 998 2/15/2008 My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2. I have tried using vlookup but it only works for the first match and doesn't check for other values below is the function that i tried =if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")
View Replies!
View Related
Delete Unique Entries
I was wondering if there might be a better way to write this macro. What it does is clears unique items from a Range( leaves duplicates ) I've looked all over the net I can find all kinds of function and subs to remove duplicates but haven't been able to find anything that just removes single entries. I"ll bet there's a more elegant way to write this maybe using a Collection or a Dictionary. Sub Dummy() Dim MP1_Rnge As Range Set MP1_Rnge = Range("A1:A100") For Each Cell In MP1_Rnge If Not IsEmpty(Cell) Then If Cell.Row = 1 Then..........
View Replies!
View Related
Identifying Unique Entries
I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher). So, for example, if i had the following values in column C:One Two Two One One Three Seven Two Four Seven One Three I would want the following values in column E:One Two Three Seven Four
View Replies!
View Related
Sum Unique Entries
I have attached a sample workbook to try and explain what I'm after. Basically I want to sum the amount of occasions a person has had checks and each occasion could have 1,2 or 3 individual checks.
View Replies!
View Related
Choosing Unique Entries
I'm trying to write an Excel program to calculate golf handicaps. In column A, I have 20 unique dates. In column B, I have corresponding 20 scores. In column C, I'm trying to create a corresponding notational mark, "*", indicating the 10 lowest scores that are also the oldest. However, I'm running into problems as I may have duplicate scores with different dates, i.e. 3/22/06 will have a score of 85 and will be the 10th lowest score, but 4/1/06 also has a score of 85.
View Replies!
View Related
Extract Unique Entries
I have a list with many names, several of them appearing more than once. I would like to extract the unique entries an then make the list of them. I can do that using the filter. But can I do it with formulas ? (In order to, whaterever change is made to the first list, get the extracted one immediately updated).
View Replies!
View Related
Filtering Unique Entries
I have this 2 columns, A(region) and B(location), and I need a macro to filter the unique entries in Columns A and return both the unique values in A and their corresponding values in B. I've attached a file on it with sheet 1 having the data to be worked on and sheet 2 being the output that I require.
View Replies!
View Related
Copying Unique Entries To New Spreadsheet
I'm trying to write a macro to use as an auto-update feature for a spreadsheet. I currently use Data validation on an input cell to allow the user to select from a list of product types. I'd like the update to affect this list, so new product types are always available to the user. However, the list of product types is in another spreadsheet and contains numerous repetitions. I've managed to get the following code together based on what I've used before and what I've found searching so far, but it seems to have a bug in it as it wont always work. I also need the original copying of the list to be done to either the new spreadsheet of a different page in the original spreadsheet, but every time I try this it causes an object error. PHP Sub Macro3() ' ' Macro3 Macro ' Macro recorded 05/12/2008 by Information Technology ' Workbooks.Open Filename:= _ "T:SSTCCDEngineeringBackthin_dataPhotolithMASK_DETAILS.xls" Sheets("Mask List").Range("f4:f2000").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("a1:a3"), CopyToRange:=Range("E1:E2000"), Unique:= _ True Selection.Sort Key1:=Range("E1:e2000"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("E:E").Select Selection.Copy Windows("MASK_DETAILS.xls").Activate ActiveWindow.Close Windows("Dry_etcher_log_B.xls").Activate Sheets("Calc Sheet").Select Columns("M:M").Select ActiveSheet.Paste End Sub
View Replies!
View Related
Find Sum Of Unique Entries
I am using excel 2007. I am trying to find the sum of unique entries in a table such as below for each respective date. DateClient1/01/2010ABN1/01/2010ABN2/01/2010BNP2/01/2010HSBC3/01/2010ABN4/01/2010BNP4/01/2010HSBC4/01/2010ABN5/01/2010BHP The results should appear as Count Unique1/01/201012/01/201023/01/201014/01/201035/01/20101 What formula would I use to calculate this unique count?
View Replies!
View Related
|
TRACKER
