I have over 60,000 rows of data. In one cell of each row is a "student ID". I need to determine how many times a given ID appears just once, how many times an ID shows up twice, three times etc.
View 6 RepliesI have a spreadsheet that users are filling in using a userform. Due to the fact that some data may be pre/post dated for entry I am trying to find a way that I can count the number of records per month.
What I have been trying to use (with no luck) is COUNTIF:
Is there a formula that I can use to Count how may records exist in a group.
Example. I have records labled M right, M left, N right, N left,.....U right, U left. Each letter represents a server cabinet
In a input cell, I can select M right.
In a second cell, I can select U left.
I need to have the formula calculate the number of Cabinets'
(M, N, O, P, Q, R, S, T, U) that it takes to cross the cabinets = 9
The problem I am having is that the formula has to change based on weather they select the left side or right side of the cab.
Example.
First input cell = M right
Second input cell = U right
I need to have the formula calculate the number of cabinets
(M,N,O,P,Q,R,S,T) that it takes to cross the cabinets' Note: U is not counted. = 8
I am using Access as a backend and Excel as a frontend. I want to count total number of records for todays where Time<13:01
Modify the following code accordingly?
[Code] .....
I have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?
I attached a pic illustrating what i want to accomplish.
QQ截图20140429104406.png
I attached a print srceen of the excel worksheet.
In E5 i want it to show the count of records that belong to group 1 (C5) with category 1 value="1"
In F5 i want it to show the count of records that belong to group 1 (C5) with category 2 value="2"
creating a formula to count the number of rows in a different worksheet if a cell has a value of "1" or "2" (these are the only two allowed values) AND a status of "'on track". also need to add values in other cells within the different worksheet if the value is "1" or "2" AND has a status of "on Track".
View 9 Replies View RelatedI need to count how many rows of records there are on the page labelled Data.
this is what i wrote...
=COUNT(Data!A3:A1995)
doesn't seem to be working correctly
there just want to ask about on how to count records using array attach here is my sample file.!
View 6 Replies View RelatedI run a work order report on a daily and weekly basis and I am looking for a formula that will find all dates in L:L that are greater than 9 days old. I can't seem to work out the syntax. The cells in L:L are formatted as DATE and TYPE *3/14/2001
View 5 Replies View RelatedI have a challenge when using autofilter.
In the attached file I have sales reps with sales numbers.
I found out from Ozgrid to use subtotal formule in C2 to sum only lines visible. Ozgrid, thx a lot for that input.
However, in cell C3 I want to count number of records, but when filtering sales rep "A", then formula "counta" still counts all records, i.e. 27.
What formula should I use to count only visible records, e.g. 7 records for sales rep "A"?
I need to count the number of records having hospital admissions within one year prior to the index admission. The list of all admissions is in one worksheet, the index admission in another worksheet. They can be matched on ID number.
View 3 Replies View RelatedHow do I create a formula that will list the records with 10 highest results
and if a tie, list the tie record with the lower value next.
Each record is is double digit value from 00 to 99:
- the records are in range ff7:ff106
- the record results in range FG7:FG106
- List the highest rankings in FH97:FH106
I have a worksheet with the following structure:
Column 1: Date (from 11/10/1996 - 31/12/2000)
Column 2 and onwards: Price (in numeric form, in which there are N/A records entered as text)
What I want to do is using VBA codes to find out how many "N/A" records appear in each month, and if there is more than 10 "N/A" records in a month, I would discard the months' record (set the records be -99).
I have records entered in sheet 1 named "Data". The "Data " Sheet contains Model wise records with serial nos and having status as either ZERO or ONE. Zero indicated that the particular model is out of stock while One indicates it is in stock.
When I filter the data using Auto Filter for records having Status 1, a list of records are displayed. I want to create a summary report in sheet 2 wherein it displays the no of occurence of a particular Model.
What I have observed is that whenever I filter for Status 1 & within it for Model A, it displays "3 of 12 records found" in the status bar. Is it possible to capture that "3" from that message displayed. Or is there any way to count the occurence ?
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 RelatedI 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 RelatedI'm using the following formula to get a count of records that have a matching function name that is in B10.
=SUMPRODUCT(--('Oct-2007_FunctionSurvey'!$T$2:$T$1498=B10))
My Problem: There are duplicate entries in my recordset based on COl A (AppID).
How can I change the formula to only count the occurences of the B10 values based on a unique AppID (col A)?
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.
I need a formula that will:
Count unique records in column C
Where value in column N = "ABC"
And value in column I = "XYX"
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)
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.
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.)
I have a medication start end and admission date.
and i want to assign a number value of 1 to all the records that the medication start date is 2 days after the admission date. How do I do this using the ifs function.
Here is a sample of data that I have:
code name lastname
TS19SylvainBrook
TS19SylvainBrook
TV04ValérieMusoni
TV04ValérieMusoni
VB05BenValiquette
VB05BenValiquette
VB05BenValiquette
VI02IsabelleVanasse
VR07RichardVivo
VR07RichardVivo
VR07RichardVivo
WD01DanyWilliams
WD01DanyWilliams
WD01DanyWilliams
How can I quickly get the total number of employees from a list like this one? the first field is a unique key (1 code per employee)
In this case, the desired result would be 6. (the count function does NOT give the desired result..)
I'm trying to create a userform which calculates and records the total number of hours a user puts in, however it can not exceed 40 hrs.
Dim intProject1 As Integer
Dim intProject2 As Integer
Dim intProject3 As Integer
Dim intTotal As Integer
intProject1 = txtProject1.Text
intProject2 = txtProject2.Text
intProject3 = txtProject3.Text
intTotal = txtTotal.Text
If txtTotal > 40 Then
txtTotal = txtProject1.Text + _
txtProject2.Text txtProject3.Text
txtTotal.Text = Format(txtTotal, "currency")
Else
MsgBox ["Total exceeds 40 hours."]
End If
If you guys could help me out I appreciate it
Thanks
End Sub
Private Sub lblProject1_Click()
I have various month's data - forex tick data - each month has between 12 and 14 million records and comes down as a .csv file.
I would like to analyse the data in Excel
How I can breakup the huge .csv file into manageable excel parts?
I need to pull a reference number and assign it to all records in a particular section. The code I recorded keeps going back to the first section. Also would like it to repeat until all records have the reference number attached.
06010-100Beginning Balance07/09/2004AD06011-10007/23/2004AD06011-10008/06/2004AD06011-100
06011-100Beginning Balance07/09/2004AD07/23/2004AD08/06/2004AD
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/14/2008 by acantu
'
'
Selection.Copy
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select
Selection.End(xlDown).Select
Range("C63").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
End Sub
I am trying to use a simple formula: COUNTIF($CT$2:$CT:$430749,CT2)
I am trying to fill this down all the rows (430,749 rows). The formula behaves as its supposed to up to around row 650. After this point, all resulting values are all the same, which happens to be the same value that was returned from the original formula in C2. However, this is not correct. It's like the formula just stops working after row ~650. I tried F9 without success.
Is it possible to autonumber records created with data entered with a user form?
If it is possible - I would also like to know how I could display the current record number on the data entry form....