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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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 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 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
3 Variable Unique Count
I am trying to find the number of unique 3 variable combinations there are in my rows of data. I have tried the frequency function but must be missing something. Attached is a sample of what I am trying to achieve although there may be a better way of portraying the data.
View Replies!
View Related
Count Unique Months Between Date Ranges
I'm scrambling my head to put this together. Could somebody show me how this is done? I have two rows of data that contains dates in the mmm-yy format. The first row is the start date of the activity and the second row is the end date of the activity. Thus, in each column i would have an activity with its start and end dates. If i want to total the number of months taking into consideration only unique months (counting over-lapping months just once), how do i do that? These dates could have breaks in between, too. Like if an activity ended Jul-08, the next could begin Dec-08. I am able to count the months using the datedif function, is there a match or other function that i need to use?
View Replies!
View Related
Count Unique Records In A Filtered Range
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 Replies!
View Related
Unique Count Of Range - Vba Coding
how to count unique records, and I can only find formulas, and not code. This code works fine, and I get the right "actual" count, but I need to change it to a unique values count. Sub CountEmployees() lastrow = ActiveSheet.UsedRange.Rows.Count For t = lastrow To 2 Step -1 If Cells(t, 8).Value <> "" And Cells(t, 8).Offset(2, 4).Value <> "" Then Cells(t, 12).Select Selection.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select List = Selection.Cells.Count ' Need to make this unique count Cells(t, 9).Value = List End If Next t End Sub
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
Extract Unique Values From Column B Between Date Range Textbox1 And Textbox2
Sheet1 has following two columns ----A----------B 01-07-2007---10 01-07-2007---20 02-07-2007---30 02-07-2007---40 02-07-2007---40 03-07-2007---60 03-07-2007---70 03-07-2007---70 03-07-2007---90 04-07-2007---100 04-07-2007---110 Sheet2 has following three cotrols Textbox1=02-07-2007 Textbox2=03-07-2007 combobox=? I want to Extract Unique values from column B between date range textbox1 and textbox2 and finally how to add these unique records in combox1
View Replies!
View Related
Searching Date Range With Mm/yy Variable
I have vba DATE variable 'FirstDate'; I assign it from a cell with say, 01/12/08; How do I then use that variable to search range of dates, without using the day and searching by only month and year? Currently, I get the error: "Run-time error '91'; Object variable or With block variable not set" at the point of With Selection.Find(FirstDate).Select I have been going in circles many times on this type problem: managing clumsily to resolve in the past using: Format(Firstdate,"dd-mm-yy"), text-to-column, NumberFormat etc
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
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
Count Certain Date In A Range
I have a problem if column A has 10 dates (some the same) i need to count the seperate dates ie if A1;A10 has 4 dates of 24-09-09 i need to total that many dates which is 4 to go into a cell named Thursday 23-09-09 will add up and go in wed and so on sorry no example no winzip in work.
View Replies!
View Related
Count Of Value Within Date Range
I thought my formula would return the number sold (SLD) from column A, but it returns 0. Any idea where I'm messing up? {= SUMPRODUCT(--(A2:A30="sld"),IF(B2:B30>=D3,IF(B2:B30<=D4,A2:A30)))} CREATE TABLES LIKE BELOW? --A--- ----B----- -------C-------- ---D---- 1 Status Close Date 2 SLD 04/26/07 Full Year Stats 3 SLD 04/27/07 From Date 04/20/07 4 SLD 05/02/07 To Date 04/20/08 5 SLD 05/03/07
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
Unique Entries & List
I have the below table of data, in Column A there is a list of entries and there may be duplicates in them, what I want to do in column B is be able to list all the unique entries in there and basically remove any duplicates. AB1DETAILED LISTUNIQUE LIST2Holden 3Ford 4BMW 5Mercedes 6Ford 7Ford 8Audi 9BMW 10Holden 11Ferarri 12Holden 13BMW 14Toyota 15Toyota
View Replies!
View Related
Formula To Return Unique Entries Only
I am seeking to pull names from a list on one worksheet in to particular cells on another worksheet using each name only once. I need a function or series of them that can accomplish this. I have been trying to figure this out for a couple of months with no luck as I always get into some form of circular reference....
View Replies!
View Related
Counting Unique Entries From 3 Workbooks
I need to count the number of unique names (column A) on 3 separate worksheets. Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each. I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names. To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))
View Replies!
View Related
Count Occurrences Within Date Range
I need to count the number of occurrences of a range of dates in one column. These are not unique dates, but dates between two date periods i.e. number of dates falling between 02/07/07 and 09/07/07. I have tried using the COUNTIFS function and inserting the column range (e.g. B7-B57) and then searching for dates <=02/07/07. However, what I need is to search for the number of times any date in a given period occurs i.e. number of 03/07/07s in a period from 02/07/07 to 09/07/07.
View Replies!
View Related
Count Of Dates Within Date Range
I've got a 2007 Excel spreadsheet that will count dates within a range using the COUNTIFS function. I am having to send this spreadsheet to users that only have Excel 2003. I need to know how to convert this so it works in 2003. Since I'm doing this for a fiscal year I have to use a date range. Here are my two formulas I'm trying to convert: =COUNTIFS(D9:D25,">3/1/08",D9:D25,"<6/30/08") and =COUNTIFS(D9:D25,">7/1/08",D9:D25,"<6/30/09")
View Replies!
View Related
Count Based On Date Range
I have one worksheet with 2 columns: A - Dates, B - User IDs. In another worksheet I would like to have two columns: A - User IDs and B - Count. The count in Worksheet2, column B would be a count of how many instances the user ID in Worksheet1 appears before or on a certain date. 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
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
Formula To Count Occurrences In A Given Date Range
I am needing to count the number of calls in a call center between certain hours by date. Column A is the date, Column B is the time of the call. I am needing to know how many calls occurred on X date between the hours of y and z. I have tried various forms of the countif and sumproduct but can't seem to get any of them to work for me. Here is the graph I am trying to populate: Date Hours 12a-4a 4a-8a 8a-12p 12p-4p 4p-8p 8p-12a 12-7-09 12-8-09 12-9-09 12-10-09 12-11-09
View Replies!
View Related
Count Strings/Text Within Date Range
I am very comfortable with VBA but I usually avoid using formulas if at all possible (unfortunately, I have, formulas are unavoidable (by request).. so formulas it is) I will try to explain Column A is a list of dates (ever expanding) Column B is a list of names ( multiple entries of various names but aligned with various dates) Column H is a list of names (each possible name which can appear in column B, no duplicates) Cell I1 = A Date Cell J1 = A Date (forming a date range) Basically what I need to do is: = COUNTIF(B:B,H2) - but only count it if it is withing the date range (>=I1 & <=J1) I have tried wrapping it in an IF statement but I can't seem to get it to work.
View Replies!
View Related
|
TRACKER
