# Function / Formula In Excel To Count Unique Values Matching Criteria

Jun 4, 2013

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

## Count Unique Values Based On Matching Criteria

Jan 9, 2007

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

## Count/Sum Unique Values Based On Criteria

Feb 19, 2009

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

## Count Unique Values With Specific Criteria

Dec 3, 2007

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

## Count Unique Values With Multiple Criteria And Using Wild Cards?

Oct 18, 2012

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

## Count Unique Values If Adjacent Cell Meets Criteria

Apr 30, 2014

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

## Count Unique Records With Multiple Criteria With Formula

Sep 18, 2006

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)

## Excel 2013 :: Count Unique Entries In A Table That Match A Criteria?

Jul 11, 2013

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.

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

## List Unique Values In Excel And Count Occurrences Of Each?

May 25, 2014

I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.

Col A
ABCD
ABCD
ABCE
BDCE
BDCF
ZXYE
ZXYE
YEZY
CDEF

Col B is where i would like to list all the unquie vaules with a count e.g.
ABCD 2
ABCE 1
BDCE 1
BDCF 1
ZXYE 2
YEZY 1
CDEF 1

## Filter And Count Unique Values In Single Formula?

Mar 18, 2014

I need to count the unique items in a range that don't contain the string "option". I would like not to use VBA for that but I can't make it work with a simple formula.

For now, I have :

[Code] ......

That doesn't count the cell with the content "option" and count correctly the other unique items. However I need to ignore any cell that contains the string "option" (with wildcards).

## Excel 2010 :: Count Unique Values In Pivot Table

Jun 20, 2014

I have data set up like the example but then for 1797 lines.

D39BKYes
D39BKYes
D39BKYes
D39BKYes
GHFLBNo
R80FANo
R80FANo

[code].....

What I need to get in a pivot table is: two columns or rows (Yes / No) and the unique count of the code.In this case is should show Yes: 2 and No: 3

## Excel 2010 :: Count Unique Values On Pivot Table

Jun 21, 2013

How do count unique values in category in pivot table. (my table, im taking data from ms query). I am using excel 2010

Pivot table example: the result i want is the "no of types" as my data only show "category" and "types".

Category
Types
No of Types

Fruits
Apple
Pear
Orange
3

Vegetable
Cabbage
1

## Formula To Count Unique Values Within A Group For Pivot - Paste Special

Jun 26, 2013

I use the following formula to count unique values within a group for a pivot:

=IF(SUMPRODUCT((\$CO\$2:\$CO55681=CO55681)*(\$E\$2:\$E55681=E55681))>1,0,1)

When I paste values over the formula it takes around 1 hours to do the 70k cells which is excessive. I want to know if there is a way to speed it up?

I tried doing an =cell and then paste special that and it works really fast but when I delete the actual formula's again the whole sheet locks up.

## Formula That Counts Number Of Unique Text Values If Meets Criteria

Nov 6, 2013

I have been using this function to count the number of unique text values in a data set:

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).

## Dcount Function/Formula: Count Based On Two Different Criteria In Adjacent Columns

Jun 1, 2007

I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.

Situation:
I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this

jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET

and I want to have a count of how many RET's there are for Initials JP ect

## 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 ?

## Formula/Function To Return Multiple Values Based On Criteria

Aug 25, 2006

I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?

## Count Number Within Slabs Through Excel Function / Formula

Feb 23, 2014

I have the following list

Column A Column B

Name of Parts Amount
A 4500
B 5500
C 8000
D 12000
E 4300
F 28000
G 1000
H 7000
I 10000
J 5000
K 11000

Total No. 11

I want the result as follow.

Slab No. of Counts

0 to 5000 4
5001 to 10000 4
10001 and Above 3

Total 11

## Copying A Row Based On Matching Unique Values

Jan 26, 2009

I have the following code which takes a unique value from 'sheet 1', and searches for the value in 'sheet 2'. If there is a matching value in sheet 2 it will copy the full row into sheet three.

I need to extend the code so that all values from 'sheet 1' are copied to 'sheet 3' even if there is no matching value in 'sheet 2'. If there is a matching value in 'sheet 2' however I need the entire row to be copied to 'sheet 3'

Sub moving()
Dim c As Range, d As Range
Worksheets("Sheet2").Activate
For Each d In Worksheets("Sheet1").Range("A1:A500")
For Each c In Range("A1:A15")
If d = c Then
c.Resize(1, 70).Copy
Worksheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Exit For
End If
Next
Next
Application.CutCopyMode = False
Worksheets("Sheet3").Activate
Range("A1").Select
End Sub

## Count Matching Multiple Criteria

Jun 25, 2007

I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got

## Count Unique Text Values (number With Text And Quotation Mark) With Formula

May 14, 2012

I am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:

Code:
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))
).

Ticket Number
T20110819.0527
T20110830.0339
T20110901.0060
T20110901.0060
T20110907.0042
T20110907.0042
T20110908.0186
T20110908.0186
T20110908.0186
T20110908.0186

[code].....

## Count Cells Matching Multiple Criteria

Jun 2, 2007

I am using the below code to count records in a range that meet multiple criteria.

myCount=0
For Each dCell In Range("MyRange")
If dCell = var1 And dCell.Offset(0,-1) = var2 Then myCount = myCount + 1
Next dCell

Is there a faster way to count multiple criteria in a VBA procedure than what I'm doing, but without using worksheet formulas? I tried "WorksheetFunction.Sumproduct" like you would in a worksheet formula, but that didn't work (unsurprisingly).

## Count Values In One List And Then Narrow / Combine Values Based On Criteria And Count Those

Jan 20, 2014

I 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

## Unique Count By Criteria

Mar 28, 2008

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 With Criteria?

Dec 3, 2012

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 With Multiple Criteria

Jul 7, 2009

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

## Count Unique When Multiple Criteria In One Range

May 17, 2012

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.