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
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.
Having a large spreadsheet with Columns A to F filled with data.
I'm trying to create a formula that's count the unique value "Order no." (Coloumn F) but only the ones between certain Departments Column(H) a certain "Error type" in Column(J).
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'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 have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))
Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.
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.
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 RelatedIn 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
If 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 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 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'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.
For 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 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.
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.
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 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.
My data is like:
A1: 5 -- B1: X
A2: 5 -- B2: X
A3: 5 -- B3: Y
A4: 3 -- B4: Y
A5: 3 -- B5: X
A6: 8 -- B6: Y
A7: 2 -- B7: X
A8: 3 -- B8: Y
A9: 1 -- B9: X
I want the count of unique numbers in column A based on the criteria i have on column B.
So the count for X should be 4 and for Y should be 3..
Can i achieve this using formula only?
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
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)
2013
Current Year
Q1-13
Q2-13
Q3-13
Q4-13
Totals
Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00
[code].....
I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.
I'm working with a data set (attached) that tracks financials at both the job and item level (in my customer's data - a unique job can be made up of a single or multiple items).
I've successfully built a formula (in column C of the attached) that counts the total number of items and their associated $ values that were invoiced in a given month based on my customer's requested item-type categorizations (i.e. "Exact Match", "Similar", "Inferred", "Cost-Plus").
The problem I'm running into is building a formula that counts the first unique instance of the Job ID associated with those items. For example - in cell C33 on the attached - The total number of items invoiced in June that were "Exact Match" was 10. These items were spread across 9 unique jobs - and that's where I'm struggling - to build a formula that only counts the first unique instance of the corresponding Job ID in Column C.
The formula I'm having trouble with is located in Cell D12 on the "Dashboard" tab of the attached.
The formula as I have it is returning a result of 8 - because it's summing the "1"'s in Column I on my "Invoice Data" tab - however it should read 9 (Filter Column Q on the "Invoice Data" tab to "June", Filter Column AQ on the "Invoice Data" tab to "Exact Match", and then sum the unique job IDs in Column C).
Excel_Forum_Example_Dataset_7.24.14.xlsx
So here is my dilemma and I haven't been able to find anything to exacly help me out:
I have a large (100k+ records) sheet of sales invoice details.
I need to add a total item line per invoice number to each record ....
Card Number
XXXX5512
XXXX5512
XXXX4211
XXXX4510
XXXX6134
XXXX9035
XXXX6002
XXXX8653
XXXX7826
XXXX8819
XXXX8315
XXXX0274
I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.
I want to count data that corresponds to 4 criteria for data located in Columns B, C, D and E. However, I only want to count only the unique values in Column A. How do I improve on this formula?
=SUMPRODUCT(($E$2:$E$5=J$2)*($D$2:$D$5=I$2)*($C$2:$C$5=H$2)*($B$2:$B$5=G$2))
See data below
