I'm trying to get a forumla to tell me if a filtered column is showing a unique entry.
suppose column A has as follows
A1 - Bob
A2 - Bob
A3 - Sam
A4 - Bob
A5 - Sam
Now, if I filter by ANY column, I want to have a formula that will tell me if the filtered/visible name(s) in column A is only 1 name. Mind you, if the user filters by criteria in a another column, the unique name may show in column A, only once or multiple times depending on how the other column is filtered.
I've tried count, counta, countif and such but doesn't seem to deal with what I am trying to do.
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.
I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).
What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.
Picture: Column C Column D 111111 M 111111 M 111111 M
[Code]....
Currently calculates: M=9, F=2
Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.
Equation currently using to count only filtered values (in this case "males" and "females"): ="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))
How can I count the number of unique values in a column?
I have a spreadsheet that lists transactions that occurred over a one-year period (2007). I need to determine the number of days our facility was operational. No transactions on a particular date (25-Dec-07, for example) and the facility would be considered to have been closed on that date.
On the days that the facility was open, multiple transactions would have occurred on those open days and those multiple tranactions are all reflected in the worksheet.
So I need to count each occurrence of '01-Jan-07', '02-Jan-07', and so on.
I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.
I have tried different formulas, but fast running out of time
I have attached a modified sample from the main spreadsheet
I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.
I'm trying to count the number of cells that contain a date in a filtered row. I've been using the following formula, but it keeps coming back with a value of 0, even though there are 3 in the filtered row;
I’ve got following formula counting different kind shoes that are black and sneakers and above formula work very well. What I wonder is if you can convert above formula to count these items in a filtered list. Could you use the subtotal-function in any way?
I'm trying to calculate the number of rows in a matrix that have a complete row of data. I would noprmally do this using nested if formuals and a count function, but the columns of the matrix are filterable so the count won't work.
Is there a combination of functions that will adapt as I filter data?
Essentially I want to show how many loans funded in October 2013, from ABC Lending, and I want that result to show in cell K9 of the Sample Output sheet. However, since I'm dealing with filtered data, I cannot figure out the correct combination of formulas to make this happen (e.g., SUBTOTAL, VLOOKUP).
Specifically, I'm stuck with the combination of a) counting the results that meet the monthly date criteria, combined with b) the filtered results. The single cell that I want to solve is K9, on the attached "Sample Output" sheet. Once I see how this cell works, I can figure out the rest. This cell is looking for the number of loans that funded in October 2013, from ABC Lending.
The filtered results are located on the "SCORE Data" sheet. The filter is already configured to only show results for ABC Lending, and the relevant column is labeled "Funded" (column "I"). Since the "Sample Output" sheet is looking for loans that funded in that month, and not a specific date, I created column "U" on the "SCORE Data" sheet, to convert specific day-dates to month-dates.
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.
I have a filtered worksheet -WB1 (filtered by Column "B"). I want to count the number of cells or rows in column "B"(Only the filtered ones) of WB1. Copy out that exact number of cells from another workbook(WB2) from the bottom moving up(Column "A") and paste it into WB1 column "I".
WB1 - Count Filtered Cell/Row with reference to Column "B" WB2 - copy Cell count bottom to top of Column "A" WB1 - Paste into Column "I"
I've got a list that's about 15,000 rows long. Column A has a date and column B has an alpha numeric ID. The list is sorted in order of oldest date at the top, most recent at the bottom. The range of dates is from 1/1/2007 through 3/23/2009. Both the same date and ID show up multiple time throughout the list.
I need to figure out how many unique ID's show up on the list in each given calendar month. I could manually break up the list into months, use an advanced filter and then run a count formula, but it seems like there must be an easier approach.
Ok so I have an excel file, that has 20,000 records/rows of data. There is a column called <STATE>, which could be PA, MD, CA, MT, etc.
I need to know how I can count how many times each state is populated and return the results to a new created sheet called <REPORT>. See example below.
I have a column (within a database) containing hundreds of Project Numbers (e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear more than once in the column. I need to count the number of unique Project Numbers within that column, based on the criteria of another column. Is there a way to do that using a combination of the DCOUNTA function and a formula?
I picked up a great formula from this website to count unique values =SUM(IF(FREQUENCY(K19:K205,K19:K205)>0,1)) and it works great. Thank you to whoever sent it (sorry, forgot who)
I am trying to find a formula to do the same for text.
We've got a column of names. A single name might appear once or two hundred times. The column is over 25,000 lines long, so we don't want to have to count how many different users there are (besides the likelihood of doing it wrong!). Is there some way to make Excel count the number of unique entries in the list, ignoring multiple repetitions of the same name?
I have a data which contains the number of customers who have logged cases with us to find a solution. So the data looks like as shown below;
Case IDCategoryCountrySubject QuestionClosing Details2550EMEADenmarkHi Please help me with the follwing orders, ticket number is JN567786Contact the retail shop12550EMEABelgiumHi please note that the product KA12QT seems to be having hardware problems. the ticket number is JN567786.
Technical issue fixed, DCK JN56778612876APJAustraliaProblem with the music system, resolution required. The ticket Id logged is JN782238 new CD provided to resolve1
What i need to do is find out the unique ticket ID (eg: JN782238) from all the description provided from column 'Subject Question' and 'Closing Details'. All the ticket numbers start from JN... , however it is very manual for to check every row has a ticket number. I have to do this for many sheets and the data contains atleast 20 to 25000 lines.
Inorder to find this unique ticket number i search through filter by giving the criteria as JN1 and keep doing untill i reach JN9. Once i filter through each of these criteria, i add a number "1" in the last column indicating that this row contains a ticket number.
After completing for all the rows, I add a pivot to check the final count of the the unique numbers, But again i need to remove the duplicates from these count, as there might be several descriptions with the same ticket Id (eg:JN789899). So i add field "Case ID" to the row area in pivot and count of these numbers in the 'Value area' (pivot). Then i look for case IDs with numbers more than 1. I f i find 2 or 3 for a particular case Id then i search this case id in the data and delete one of the count ('1') in the last column.
I have 2 columns with approximately 200K lines of data. There are only 84 and 823 unique values in each column respectively. On my report.
Column A is essentially a customer list, column B is a product list. I am trying to find out how many unique/individual products a customer orders. I have a separate column that has the customers in it (Column G), and in Column H, I want it to count the unique products ordered by customer.
I changed some of the data, but essentially what I attached is what I need.
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))
I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements,
I have created a calendar in Exel for individuals to put in their time at the bottom for everyday I have put in a total. Is there a formula that I can sum the dates, pick a certain time from those date and get my total hours for that particular week?
