Counting Unique Cells Based On Another Cell
Aug 1, 2008
I have 2 columns that I need to use simultaneously to conclude something.
Column A
Row 1: Dog
Row 2: Cat
Row 3: Bird
Row 4: Dog
Row 5: Dog
Row 6: Dog
Column B
Row 1: See Vet
Row 2: See Vet
Row 3: Get Rest
Row 4: Get Rest
Row 5: Get Rest
Row 6: See Vet
Okay, so I have these columns, and I want to count the unique number of animals that need to see a vet, or get rest. Based on the table above, there are 2 unique animals that should see the vet (Dog and Cat), and 2 unique animals that should get rest (Dog and Bird). how to do this in excel.
View 5 Replies
ADVERTISEMENT
Aug 11, 2007
Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............
I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.
View 4 Replies
View Related
Dec 15, 2009
I'm having difficulty trying to write a formula for the following type of data:
[data] ....
This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...
View 9 Replies
View Related
Jun 9, 2006
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?
View 11 Replies
View Related
Apr 24, 2014
I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.
I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.
However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....
View 14 Replies
View Related
Aug 21, 2014
Formula which will count unique values in column A based on condition in Column B which "y" .....
Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....
View 3 Replies
View Related
Jan 21, 2014
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program
Clinic Title
Start Date
[Code].....
formula that will automatically give me that answer?
View 2 Replies
View Related
Aug 11, 2008
I am trying to give a cell a unique ID based on the contents of 2 other cells.
Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.
View 9 Replies
View Related
Jul 10, 2013
I am attempting to count how many cells have different values in a table while not counting the N/A's and 0's found in the table. From what I'm finding online, I see lots of formulas set up with frequency functions, but none of them are set up to exclude anything - just find unique values. I need to count the cells with values other than 0 and N/A going across each row and not count the same value twice
Here's an example of what the table looks like (the real one is over 1,000 rows and 50 columns) with column C being where the formula needs to go. I put what I would like to have returned in the cells.
C
D
E
F
G
H
5
2
N/A
N/A
0
43263526AF
324GFDS
[Code] ........
View 5 Replies
View Related
Jan 6, 2008
I have the following data in 1 of the tabs.
[TABLE]
ClassSequenceAB
10SE132422
20SE23212
20SE321
20SE23425
10SE332455
15SE132412
10SE234
[/TABLE]
I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:
[TABLE]
ClassAB
1032
1511
2012
[/TABLE]
Can this be achieved through a formula?
View 8 Replies
View Related
Jun 19, 2013
Here's a link : [URL] .....
What I would like to do is create a column that will count the number of cells in each row whose background color is not red or yellow. The yellow background color was input manually, and the red background color was input through conditional formatting.
I have a spreadsheet full of GPS locations of different pumps on different dates. Each column represents a different pump, and each row represents a different date. The cells on this sheet contain one of two things - either a set GPS coordinates or the word "Repair" and a description of the repairs done. A few days ago I went through and checked the GPS coordinates to see which pumps were where every day during the past three years, and changed the background color of each cell containing GPS coordinates determined to be off-site to yellow. I then used conditional formatting to change the background color of each cell containing the word "Repair" to red. Now I need to go through and find out how many pumps were on location, not being repaired on each day.
Here's a link to a sample workbook. Any cell containing the word "GPS" just represents the fact that there are GPS coordinates in the cell. Their format is basically "(#####, #####)".
pump location example spreadsheet.xlsx
View 2 Replies
View Related
Oct 30, 2013
I am using Excel 2010.
BTES worksheet:
acct# Oct-13 Nov-13 Dec-13
13245 850
12458 850
45864
12385 1500
MainList - F2= (current month)
Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.
This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))
View 2 Replies
View Related
Mar 17, 2014
See attached file, basically need a formula that will give me the unique resources for each location. I know a pivot table would be easier but i need a formula for my model to work.
View 6 Replies
View Related
Feb 22, 2010
I’m having some problems writing a macro,
I have a spreadsheet with 2 columns. The first column is the index number (unique identifier) of the second column’s data that will repeat itself as many rows as needed. Let me try to exemplify it:
Column A (index)Column B (text)
1 AAA
1 BBB
1 CCC
2 DDD
2 EEE
3 FFF
3 GGG
3 HHH
3 III
I’m trying to write a macro that will merge the text of each identifier in the first row it appears. The result I’m looking for is:
Column A (index)Column B (text)
1AAA
BBB
CCC
2DDD
EEE
3FFF
GGG
HHH
III
I already know how to make the merge, but I can’t get the logic to make it go through each row and merge the contents, since the number or rows for each identifier varies.
View 9 Replies
View Related
Jan 3, 2008
I have a worksheet with following data in 1 tab.
[TABLE]
Seq Class
SE110
SE270
SE110
SE370
SE310
SE110
SE170
SE370
[/TABLE]
In a different tab I want to calculate the unique count of sequence for a specified class.
In above eg: for class 10 it should return 2
View 3 Replies
View Related
Feb 4, 2010
I have attached a spreadsheet I am working on. In it I need to count the number of cells populated in F5:F34. The issue I am running into it that there are formulas in those cells that determine what to display. Is there a way to count what cells are blank and not blank based on the result from a formula? In the attached for I should get an answer of 3.
View 2 Replies
View Related
Sep 19, 2013
I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.
I've attached an example sheet : Example email report.xlsx
The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.
So, in the example, the result would be 7.
I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.
View 8 Replies
View Related
Jun 10, 2008
Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.
What I want to do is look down Sheet OEE V20:V500 and get two lots of information -
The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.
I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.
I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.
Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
View 9 Replies
View Related
Mar 26, 2009
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.
View 9 Replies
View Related
May 13, 2008
how to count unique Occurneces in Excel?
For example: -
What formula can I use to count the Unique Occurences non - zero values in Column 2 for the letters in Column 1?
Column 1 Column 2
A 1
A 1
A 1
B 0
C 2
C 2
D -1
View 10 Replies
View Related
Nov 2, 2009
Couting Unique Entries
I've included a sample worksheet ...
View 10 Replies
View Related
Nov 11, 2009
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.
State:
PA
PA
PA
MD
CA
MT
NJ
NJ
Report Results
PA 3
MD 1
CA 1
MT 1
NJ 2
Can this be done?
View 9 Replies
View Related
Nov 19, 2007
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.
View 9 Replies
View Related
Mar 27, 2008
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?
View 9 Replies
View Related
Nov 28, 2009
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.
View 9 Replies
View Related
Feb 3, 2012
I'm putting together a calendar that will have show when people are off on holiday, training, out of the office or ill. But as it will identify people by name I have changed to cell style to reflect just what activity they are doing. As such I want to be able to count over a year just who has done what.
Is there a piece of code that can count based on the text inside a cell and it's style e.g. "20% - Accent1"
View 2 Replies
View Related
May 7, 2014
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.
View 8 Replies
View Related
Sep 23, 2009
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 2 Replies
View Related
Feb 16, 2006
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,
View 14 Replies
View Related
Jan 1, 1970
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?
View 6 Replies
View Related
