# Count Of Unique Values With Duplicates

Nov 29, 2007

Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)

I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...

## Count Unique Values / Text Within Range Of Cells That Contain Duplicates / Blanks And Errors

Jun 25, 2014

How I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?

For e.g., in Column A (row 1 - 10):
Proj-001
Proj-001
Proj-002

Proj-004
#N/A
#N/A

Proj-007
Proj-002

## Count Unique Duplicates In Two Columns

Jan 25, 2010

I need a one formula solution to compare two columns for duplicates and count the number of matched pairs. Need to ingore blanks. Can have matched numbers or letters. Asterik denotes a blank (empty) cell. Also only consider the first matched pair if there are more than one matched pairs.

Col A Col B
a
b a
* b
c c
d y
x f
f z
f z

In this case I only want to consider a:a, b:b, c:c and f:f, resulting in the count of 4.

## Count Duplicates As Unique Record, Sum Amount

Mar 14, 2007

I have a spreadsheet will a large amount of invoice numbers, some of which are multiple occurrences of the same number. I need to count the duplicates as one unique record and sum but I need to sum the total \$ amount of each amount attached to each occurrence.

Please look at the sample to see what I mean.

W234678 has three amounts that are added to give a total amount for that number and it is added to the count as one record.

I had this code kindly borrowed from someone else which helped me find the duplicates but it is not meeting my needs.

Dim rCell As Range, rRng As Range, vKey, lrow As Long

Set rRng = Range("F2:F199")

With CreateObject("Scripting.dictionary")

.comparemode = vbTextCompare

## Count All Unique Values (text) + Definition Of UNIQUE :D

Aug 24, 2009

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 ?

## Get Unique Values In A List Of Duplicates

Feb 28, 2007

I am confident the answer is no, but is there any formula / combination of formulae that will seek out unique values in a list of duplicates (just like filtering unique records only)?

I have a list of many duplicating sales people and I want to just create a column on another sheet that automatically sorts out the unique values (then I will sum their sales numbers with the SUMIF formula).

(By the way, the reason I just don't use the filter function is because the list always changes so I would need to keep applying it.)

## Macro To Find And Delete All Duplicates And Keep Only Unique Values

Sep 22, 2009

Looking for a macro to find and delete all duplicates and keep only unique values from a column. For example column AS has
ACLU0403598
ACLU0403598
ACLU0403598
ACLU0406600
ACLU5165518
ACLU0406581
ACLU0406581

All red items need to be removed and keep only green items. Would also like the entire row the duplicates are in to be deleted.

## Vertical Lookup To Remove Duplicates And Return Unique Values Horizontally?

Mar 3, 2014

In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.

Example: CT11:CT14 looks like
5x20
6x4
5x20
5x8
and so on and so forth..

I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.

## Macro To Give Count Of Unique Values After Comparing Comma Separated Values?

Mar 2, 2014

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.

## Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

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.

## Locate Values That Are Duplicates Then Count Them

Mar 11, 2014

I have a spreadsheet that has the following

I have 30 teams with 15 players each

I have teams in rows and players columns with their ID and then name so B2 would be 444 and B3 would be Johnson and B4 would be 429 and B5 would be Smith

What I want to do is run a formula that counts the duplicate ID's not names and then shows them in a list like below

Duplicate ID, Number of times in worksheet
444 , 23
411 , 21
211 , 15

How I can do this.

## Count Number Of Different Values In Duplicates

Apr 10, 2008

Below is a range of data i am working with, i am trying to create a formula that will count the number of different entries in column A for each different entry in column B.
ie how many differnt values are there for "Packing"

008003PICKING MISTAKE
008042UNFIT FOR PURPOSE
008035PACKING
008035PACKING
008035PACKING
007960CHANGE OF MIND
007986PACKING
007986PACKING
008050UNFIT FOR PURPOSE
008070CHANGE OF MIND
008070CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008086PACKING
008085PACKING
008085PACKING

## Find Values And Count Duplicates In A Sheet

Jul 30, 2013

I would like to find the values and count duplicates on a sheet. Here is my example:

A1
A2

Organization1
Organization2

XXY
BBA

ACC
XXY

BBA
ABC

I would do countif, but I do not know my values (this is from a report that I ran from a database) and it would take too long to find all the individual values every time I needed to run this report. So this is what I am looking for (a graph would work too).

A3
A4

ABC
1

ACC
1

BBA
2

XXY
2

## How To Count Only Unique Values

Jul 24, 2014

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‎

## Count Unique Values ....

Dec 28, 2009

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 ....

## Count Unique Values

Jan 13, 2010

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.

## How To Count Only Unique Values

Jun 6, 2014

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

## Count All Unique Values

Apr 15, 2009

Is there a formula that can be used to count all unique values in a specified column.
Example:
Col. A
123
123
Test
ABC
ABC
ABC

Result: 3 (since there are 3 unique values in col. a)

## Count Unique Values, For Specific Values

Sep 13, 2006

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.

## Find And Count Unique Values

Jun 24, 2009

I have a spreadsheet that holds customer information. What I want to do is find how many customers there were last month. I have a cell (C1) which has last month displayed as 2009/05. A1 holds the customer ID and B1 holds the date they used us. Each customer may has used us many times and I'm having a nightmare trying to solve this.

## Count Unique And Specific Values

Aug 18, 2011

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).

## Count Unique Values Between Two Dates?

Nov 19, 2013

I want to count the unique values between 2 dates. He only needs to count the values that match 2 criteria. I want to know how many unique orders were place between 01/10/2012 and 30/09/2013.

In my Data sheet:
Colomn X: Customer name
Colomn D: Status
Colomn AH: Date of order (Day/Month/Year)

I want to count the unique values in Colomn O: Ordernumbers

This is what I already have:

=SUM(--(FREQUENCY(IF(Data!\$X:\$X=\$A4;IF(Data!\$D:\$D="BOOKED";Data!\$O:\$O));Data!\$O:\$O)>0))

This formula is working. He counts the unique values in colomn O that meet the 2 criteria (Status = "BOOKED"; Customer = A4).

But I don't know how the add the date range in this formula. I want him to take only the values that are between 01/10/2012 and 30/09/2013.

## Count Unique Values With Critera

Jan 28, 2010

I have a large list of data and need to filter unique values of 1 column with a criteria.

For example:

A B
Car 1
Bike 1
Car 2
Car 2
Bike 3
Bike 3
Bike 4
Car 4

I want to filter out only the lines whereby the column A & B is the same comment/value.

Other words: both column A & B have to be unique values.

Based on the list above this means that the list should look like this:

A B
Car 1
Bike 1
Car 2
Bike 3
Bike 4
Car 4

## Count/Sum Unique Values/Entries

May 9, 2008

Can DCOUNTA be used to only count unique values?

If the above is not possible how would I do the following:

Criteria

Column H = text1 AND Column B date is between date1 & date2

Count unique values in column C

## Count Unique Values Based On Another Column

Jul 1, 2009

I am using Excel 2007, and I'm stuck with this problem.

## Count Unique Values In A Filtered Column

Dec 29, 2008

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.

## Count Unique Values With Multiple Criteria

Oct 27, 2011

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.

## Count Unique Values With Specific Criteria

Oct 31, 2011

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.

## Excel 2007 :: Count Of Unique Values

Nov 18, 2011

I would like to get the count of unique values in my 2nd column using my values in 1st column as the criteria. Below is my example of my data set.

EFGH5DeptHC

6A304794A17B86122B38B86179C39B90050D1310C82705

11C94955

12C308165

[Code] .........

Excel 2007

I would like to see the count on column H (highlighted in yellow).

## Count Unique Values Depending On Condition?

Jun 17, 2012

I'm looking to get a formula to counting unique values listed in a column depending on a condition also find the attached file for more details

[URL]