# Count Unique Items With Multiple Criteria

Aug 12, 2009

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)

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

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

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

Jun 16, 2009

Counting unique items in column (numbers)

## Count Condition Unique Items

Jan 22, 2009

Can someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).

Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)

## Count Total Items Per Unique ID

Feb 19, 2014

I need a formula to count the total items per unique id. For example

Column A Column B
33333 Baseball
33333 Baseball
22222 Hockey

Output to Column C,
ID 33333 2 Baseball
ID 22222 1 Hockey

## Count Unique Items With A Condition?

Oct 30, 2012

I've an excel sheet with 1000 rows data. I need a formula to count number of unique items in a column with a condition. Please find a sample data sheet.

I've tracking list that has ordered and delivered items for a given month on weekliy basis such as Week1(W1), Week2(W2), etc. I need a formula to count number of unique item delivered on this month.

Item 1 to item 8 delivered and item 10 also delivered in week 4, So totally I need to get the result as 9.

I was trying to use "countif" for validating and counting each row, it's throwing value! Error =1/(COUNTIFS(A:A,A2, c2:c23, ""&0)) i.e., = 1/countifs(unique items from column A, then nonblank cells in column C) but it's giving Value error..

I just need to count the unique item with another condition.

Item details
Order week
Delivered Week

Item 1
W1
W1

Item 2
W1
W1

[code]....

## Count Unique Items In A Column

Dec 7, 2007

Looking for a formula that will give me a count of unique entries (i.e. duplicate entries to not be counted.)

basically a formula that says look in D4:D4001 and count only one of each entry....do not count the duplicate entries.

## Count Unique Items In Autofilter

Jun 10, 2007

I have a Excel sheet and I have put Autofilter for a particular category I choose I need to count number of items in another column(autofilter) , for eg... there are two columns Category , Items When i choose a particular Category I need to count number of Items in the autofilter for that particular category?

## Count Items In Column That Match Multiple Data Items?

Mar 27, 2014

I need to count the total number of times 4 different values appear in a column. This formula works for one value:

=COUNTIFS(Source!\$C:\$C,\$B5,Source!\$J:\$J,\$L\$3,Source!\$L:\$L,\$H\$1)

Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.

## Retrieve Unique Items With 2 Criteria

Nov 29, 2005

I've been using the following formula from the xldynamic website detailing
sumproduct usage as a base for retrieving uniqe instances in a data series.
I've been trying to modify the formula to allow me to utilize one additional
criteria.

Original formula:
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

Here is my formula:
=SUMPRODUCT(((\$O\$7:\$O\$2710<>"")*(\$M\$7:\$M\$2710="1986"))/COUNTIF(\$O\$7:\$O\$2710,\$O\$7:\$O\$2710&""))

I'm trying to say how many unique instances are there for column O, where
column m = "1986?"

## Count Unique Items In Filtered Range

Dec 13, 2009

As the subjects states I need to count the unique entries in a filtered range.

## Creating A Macro To Count Unique Items In A List

Jun 1, 2006

I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?

## Count / Calculate And Graph Sales Of Unique Items Over Time?

Nov 24, 2013

My company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.

I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear?

## Pivot Table To Count Unique Items In Large Dataset

Oct 15, 2012

I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.

## Parsing A Row Of Multiple Items From A List Of Matching Unique

Jan 8, 2008

I have two worksheets: A and B.

Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B).

Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated.

Any particular Issue# field may have several issues in it, delimited by a comma.

Program is a program associated with the issue and this column also contains duplicates.
Worksheet B:Contains 1 column: Issue# (Col. A)

This is a unique list of issues#'s.

All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues.

A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list

There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about)

I really hope that makes sense, but if not...

Here's the best example I could come up with: ....

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

## Formula - Count Of Items That Meet Specific Range Criteria

Dec 4, 2012

Attached excel sheet below. Suggest a formula to get the count of items that fall in a specific data range ?

Count of items that specify a range criteria.xlsâ€Ž

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

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

## Unique Count With Criteria Under Distribution Matrix?

Jul 18, 2014

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

## Count Unique Records With Criteria From Another Column

Feb 14, 2008

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.

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

## Sum & Count Of Unique Numbers Meeting Criteria

Jan 28, 2009

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.

## 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 Of Unique Numbers Based On A Criteria

Feb 17, 2008

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?

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