I have this formula to give me the number of unique locations and it works fine for cell J1:
=SUM(IF(FREQUENCY(MATCH($B$3:$B$22,$B$3:$B$22,0),MATCH($B$3:$B$22,$B$3:$B$22,0))>0,1))
What I am trying to figure out is how to populate cells N4:N5 and N8:N9 for unique locations by Fruit & Vegtables by Area A and Area B. I just have some basic data for this example as the spreadsheets are 30,000+ lines long.
I am looking for a formula which will allow me to count unique values in a database, based on multiple criteria. Sample file attached (Formula required in cells J and K).
View 6 Replies View RelatedFor example, each job has a specific ref number and within that job ref number there is a number of lines of data, some of which we ignore and some of which we include. Each job will have a planning time using an old methodology (Planning A), a planning time using a new methodology (Planning B) and an actual time. There are a few other variables too such as the category of work.
I am trying to count the number of each unique ref no. within the sub category of work I am looking at (there are circa 50 sub categories of work). Each ref no. will only have 1 combination of work cat and sub category.
For example: ....
I have data in the following type
A Criteria B
A Criteria C Criteria A
C Criteria C
D Criteria 1
2 Criteria 3
Criteria 2
3 Criteria
I need a formula that will count the number of unique values in column A that have "Criteria" in column B. In this case the desired output would be 5 (A, C, D, 2, and 3), a blank cell in column A does not need to be counted.
I'm facing a big problem and I can't found a solution for days.
I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
Let me Put to you an example for you been able to understand my problem.
Example considering Range "C2:C20" exluding blank cells
(1) - To count unique values
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
(2) - To count unique values based on criteria in range "B2:B20"
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))
(3) - To sum unique values on "E2:E20" based on same criteria
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))
They all work fine. But I need to insert a merge at Row 10 for
better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.
I just don't know what to do guys ....
I need to count the amount of Unique codes in column A BUT ONLY if they are still OPEN status. Hopefully the attached example will be explaination enough (I want to automate the RED figure...
View 3 Replies View RelatedI have two ranges of data and want to count the unique number of times the values in column A occur if they have two values in column B. For Example:
ColA ColB
333310143333306344441014444430635555161246666101466661612466663063
I want to count the number of times value in ColA has the value 1014 AND 3063. My intention is to create a table that cross references the two
10143063161241014331306333116124112
I searched and found answers where multi criteria were in multi columns but I can't seem to find a solution to this means of find the unique count in ColA.
A B C D
1 Yes Red Green
2 Yes Blue Black
1 No Blue Black
2 No Blue Green
2 Yes Red Black
I am interested in finding the number of unique entries under column A where column B = Yes; Column C = Red OR Column D = Black (answer is 2)
I have a worksheet with two tabs.
First Tab
Account Name
Account Number
Second Tab
Account Name
Account Number
Account Ship Location Number
On the first sheet each account name and number only appears once.
On the second sheet there may be multiple entries for each Account Name and Number.
On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
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)
In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
Capture.PNG
At the moment I have a formula as below:
=SUMPRODUCT(--(B:B=$D2),--(A:A<>""))
this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.
Modifying my formula. I have attached the sample workbook below.
Book1.xlsx
I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.
Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return
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
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
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
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?
In one sheet I'd like the user to select from 3 dropdown lists certain predefined values.
On the second sheet there is a long list of unique cells (one column, that can't be split into usefull columns with text to columns or something). I want to find the cell that holds the three text choices. These can be in different order to make things more complex. How do I create a search that finds that one match.
Example
The user selects "AAA" and "DDD" and "FFF" from the dropdown lists
The formula should find that one cell that holds this value: "FFF JJJ GGG DDD CCC AAA". This is the only cell that holds all three chosen values in one text. In the end I would need to have the row number of that cell,
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?
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))
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 am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
I'm trying to figure out how to count by two criteria, only one of the criteria has a value among multiple values in a cell and the other is a range. I tried to use sum but it doesn't count the value and count doesn't recognize the range. I attached the file so you can see what we're trying to do.
View 4 Replies View RelatedIf I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies View RelatedI am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
View 2 Replies View RelatedI need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
i've tried to locate a matching post for my query, although a couple have been close I havn't been able to find one which matches what i'm trying to do. I have a spreadsheet which has two columns A:PersonIds and B:Month/Year. A person id can appear many times for a given month. What I am wanting to do is to perform a unique count of person id's by month. Take the following example:
ID , Month/Year
001, Jan-08
001, Jan-08
002, Jan-08
002, Feb-08
003, Feb-08
001, Mar-08
Below is what I expect the count to total
Totals
Jan-08 = 2
Feb-08 = 2
Mar-08 = 1
I've also attached an excel file with what i've just outlined. Is there a way to do this without using VBA, but with a combination of array's and nested if statements?
Count unique entries in a column according to two specified criteria.Specifically, I am trying to count the number of days in a month a supplier has a visited.Below is an example taken from the excel:
COLUMN A COLUMN B
Date Supplier
01-ago-12 Mr X
01-ago-12 Mr X
01-ago-12 Mr X
I want a formula that allows me to see how different dates Mr X has visited in each month. So in August he has visited 8 times BUT only on 5 different dates.
In the case of Mr Y I want the formula to give the answer 5 (even though he has visited 6 times in total, he has visited on only 5 different dates).
In the case of Mr X in September, the answer to the formula would be 2, since has visited on 2 different dates in September.
Unique Count situation based on multiple criteria
Scenario: we have agents from different departments who produce different sales on different plan rates
Raw Data
Employee
Plan Rate
Payout pts
[Code]....
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 have a list, 50000 rows long with phone numbers and the service provider it belongs to. I want to extract some of this information. I'm looking for a formula which can give me the following: How many unique phone numbers is there in column B from service provider number 4 and 5? The following pic is just an example of how the list is compiled. How can I get sum up only the numbers from 4 and 5 and not the whole bunch? If I use this formula
Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
=SUM(N(FREQUENCY(D1:D51112,D1:D51112)>0)). I sum up all the unique phone numbers, but I don't know how to set a "if service provider is 4 or 5" if you get what I mean. This was the first part. The second part is finding out how many times each unique number from service provider 4 and 5 is listed.