Counting Unique Values With Multiple Conditions
May 7, 2014
I need counting unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.
The formula I have is:
SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D<=Z4,G:G)))),G:G),1))
The goal is to count all of the unique value in column G:G that meet certain criteria (have ending times greater than Z3 and beginning times less than Z4, etc.). Once I get the formula to work, I'll simply drag it down to have it calculate for similar lightly changing criteria (like different beginning and ending intervals). However, the formula doesn't seem to be working. It's only giving me zero values (I have "ctrl-shift-entered").
View 9 Replies
ADVERTISEMENT
May 7, 2014
I need to count unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.
The formula I have is:
SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D
View 9 Replies
View Related
Aug 13, 2014
I am looking to count the unique amount of customers who are listed in column C based on the criteria that they purchased the product on the 1/08/2014 and that the product came from Department 3. The output I am looking for in this example would be 3. I can do it for this example but when I have 300 different customers it starts getting tricky and I'm a bit stumped on how to incorporate an array formula into a countifs() function or whether there is an alternative.
Date DepartmentCustomer
1/08/2014 3 A
1/08/2014 3 B
1/08/2014 2 C
1/08/2014 3 D
4/08/2014 3 A
5/08/2014 2 A
5/08/2014 3 D
View 7 Replies
View Related
Feb 25, 2009
I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:
2366 2005 T [number of corresponding unique values of B]
For example:
--D---C---A-B
2366 2005 T 2
(as opposed to 3)
A
B
C D
T
655 2005 2366 T
656 2005 2366 T
656 2005 2366 W
659 2005 2367 W
659 2008 2369 W
659 2006 2370 F
659 2005 2370 W
660 2005 2370 W
660 2008 2371 W
660 2006 2371 T
660 2005 2371 W
661 2005 2372 W
661 2007 2372
View 9 Replies
View Related
Feb 26, 2009
I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:
2366 2005 T [number of corresponding unique values of B]
For example:
--D---C---A-B
2366 2005 T 2
(as opposed to 3)
A
B
C D
T
655 2005 2366 T
656 2005 2366 T
656 2005 2366 W
659 2005 2367 W
659 2008 2369 W
659 2006 2370 F
659 2005 2370 W
660 2005 2370 W
660 2008 2371 W
660 2006 2371 T
660 2005 2371 W
661 2005 2372 W
661 2007 2372
View 9 Replies
View Related
Feb 22, 2011
I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.
In excel is best illustrated what I mean!
In total, 5267 lines and need to do this with all. You can do this in excel?
View 4 Replies
View Related
Apr 28, 2014
I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.
I've attached the XL file for your review of the project.
=SUM(IF(FREQUENCY(IF(('TX1'!$B$2:$B$15=B2)*('TX1'!$C$2:$C$15=C2)*('TX1'!$D$2:$D$15=D2),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0)),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0))>0,1))
View 4 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
Jun 26, 2014
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.
View 7 Replies
View Related
Nov 10, 2008
My worksheet contains a database of systems that have been installed since 6/08. Each system has its own unique serial number, however, this serial number appears more than once.
I'm trying to count the number of systems that have been installed since 9/08/08 but I do not need Excel to count each s/n entry, I only want it to return a count per s/n once to get a total of all installations since this date.
I have attached the sheet in pk zip format.
View 8 Replies
View Related
Nov 5, 2009
I am looking to get a formula that count the number of rows that match some
criteria. The sample table is listed below. I would to have a formula
calculate the # of rows in which values in column B (>=3 and <=3.9) along with values in
Column C = 0
IDRATINGAMOUNT(formula match)
003044.0 (5.0)
002992.5 0 x
000692.5 2.0
013804.0 0 x
017293.0 0 x
017832.5 0 x
019342.0 (1.0)
019013.8 (1.0)x
014823.0 (1.0)x
020762.5 (1.0)
COUNT346
B>=3, B<=3.9 = 3 rows
C=0 = 4 rows
Overall = 6 total rows
But if you combine both criteria, there are 6 rows that match. I'm having a
difficult time figuring our how to write the formula for this.
View 2 Replies
View Related
Nov 7, 2008
I am working on a spreadsheet that contains multiple text conditions that I need to keep count of. I would like to try the countif but I don't know if this will work. Here's a summary of my spreadsheet
Column F in my spreadsheet contains 7 different regions (all text).
Column D contains an open or closed option that is not validated.
In my totals section of the spread sheet I need to count each region that meets the "closed" criteria.
View 3 Replies
View Related
Aug 14, 2008
I have a large list of items and I want to be able to count and sum up the cells based on multiple conditions. Lets just say for example I have a list like this, also the list updates and adds more rows for more information, so its not confined.
xxx Open
xxx Complete
yyy Complete
yyy Open
xxx Complete
xxx Open
I want to be able to count how many xxx are Open. I assume it involves either CountA, CountIF, If, And but I keep getting errors when It searches for text. I can use CountA and have it search for text of a single columb just fine, but I cant do contitions based on multiple columbs with just a cell formula.
View 9 Replies
View Related
Oct 8, 2007
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 11 Replies
View Related
Sep 30, 2013
What I am trying to do is create a list of unique values under certain conditions. I will try to explain it as best as I can.Imagine we have four columns:
Column A contains a product, so for example Apples, Bananas, Oranges...
Column B contains names, so for example Peter, Carl, Linda...
Column C contains stores, so for example Supermarket, Cornershop, ...
Column D contains a number, so for example 1, 2, 3...
Now let's say I want to see who bought apples in the supermarket, I would like to query kind of like this:
For all rows where column a = "Apples" and column c = "Supermarket", write me Column b value and expect something like
Peter
Linda
if only these two bought apples in the supermarket.
But I need to make sure its only unique people, so if Peter bought apples at the supermarket twice, I do not want to see
Peter
Linda
Peter
Now the next step would be to see what was the maximum amount of apples that peter has ever bought at the supermarket, so Im thinking of something like:
For all rows where column a = "Apples" and column b = "Peter" and column c = "Supermarket", find me the maximum value that is ever displayed in Column D.
Like I said, I'm totally new to VBA so I am not really sure how to get started with this, but what I do know is that doing this with if statements would take me forever, so I want to give vba a try.
View 4 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
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
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
Jan 31, 2013
what function can I use to count the number of unique values in a range of cells?
View 4 Replies
View Related
Sep 18, 2013
I have the following macro (as part of a much bigger set of code that adds some formulas to to cells to return the total number of unique values. The problem is, this part of the macro takes over 10 minutes to run. Is there a quicker way of doing this or am I going to have to just wait it out each time.
The columns B and/or H is a formula. Column W is an string value.
FYI the last row it is counting to is between 4000 and 30000 lines in each of the formulas
Code:
Dim IRMatRow As Integer
Dim IRMisRow As Integer
Dim UKMatRow As Integer
Dim UKMisRow As Integer
IRMatRow = Worksheets("Matching Stats").Cells(Rows.Count, "H").End(xlUp).Row
[Code] .........
View 2 Replies
View Related
Jan 17, 2008
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.
View 9 Replies
View Related
Jan 2, 2009
I am looking for a solution other than using an advanced data filter for unique records only.
I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B).
Group A only includes records with value ="A" in the checksheet column.
Group B includes records with value <>"A" in the checksheet column.
Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated?
SEE ATTACHED:
View 9 Replies
View Related
Dec 9, 2013
I am using Excel 2007
I have 50 000+ rows that covers fiscal years from 2006 to 2014 and I want to calculate how many unique clients are there per asset type per fiscal year. In addition, the results table needs to adjust to filters i would use on the raw data..
As:
2007
2008
2008
Commodity
45
FX
Other
other
Here is a sample file: SampleData.xls
View 8 Replies
View Related
Aug 3, 2006
I want to count unique items in a list with an array formula, like the items is in 3 columns A,B,C are from row 2 to 101. Now i use an array to filter out some rows in the columns of B,C & then count unique items in Column A.
so,
column A has "30 diffrent names repeated from A2 to A101"
column B has numbers 0 to 100
column c has value either 0 or 1
now i use an formula TO filter out rows in column B & c
formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter
now what should i suffix or prefix to this formula to count unique values in column A.
View 11 Replies
View Related
Jun 9, 2014
I need to count all of the unique value in column C( Supplier Name) If the Owner is Analyst on column D and the Buyer is Mark column A.
I would be needing a formula to count it and not pivot table. I dont know how to set up an array and I just have learn how to use the Countif formula.
Buyer
Parts
Supplier name
Owner
[Code]......
View 3 Replies
View Related
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
Dec 28, 2009
I have a spreadsheet which has in one column a list of offices in Column H. In Column K there is a lst of locations. In these cells the 'location' has a value of for example ( abcdb 1234), however in a cell there may be more than one value eg (abcd 1234, xxxxx 3241,). I need a macro or formula to count the number of locations per office and also a macro or formula to count the number of unique locations per office and the number of unique locations overall. A small example of the spreadsheet. This spreadsheet can have thousands of rows.
Column H Column K Office 1 abcs 1234 office 2 locationa 2435, locationb 6521 Office 3 abcs 1234 Office 4 locationa 2435, locationb 6522 Office 5 abcs 1236 Office 1 locationa 2435, locationb 6522
View 9 Replies
View Related
Feb 16, 2005
I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows.
An example:
5CZ975 Van
25RW04 Straight
5CZ975 Van
ZJ6026 Van
So, the result I am after is 2 vans, 1 straight truck
I found an array in a another post, but it is not working properly (see cell F7 in the attached):
=sum(if(frequency(if($b$2:$b$18=$f7,$a$2:$a$18),$a$2:$a$18)<>"",1,0))
**ctrl+shift+enter**
View 5 Replies
View Related
