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

View 3 Replies
ADVERTISEMENT
Mar 5, 2014

I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).

What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.

Picture:

Column C Column D

111111 M

111111 M

111111 M

[Code]....

Currently calculates: M=9, F=2

Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.

Equation currently using to count only filtered values (in this case "males" and "females"):

="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))

View 9 Replies
View Related
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.

View 2 Replies
View Related
Jan 21, 2009

Is it possible to count the unique entries in a range based on the results of a filter that has been applied? I basically have a column with 2000+ cells that contain some matching values and I only want to count the unique entries. This will need to be a dynamic count as well as the filter criteria can and will change all the time.

View 14 Replies
View Related
Dec 13, 2009

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

View 7 Replies
View Related
Dec 19, 2010

I would need to count unique frequencies in column A, but only for a specified value in column B.

Example:

A|B

1|1

1|1

1|1

2|1

2|1

2|2

2|2

3|2

3|2

I am using Excel 2007 and found this formula that counts all unique frequencies in column A: =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))

In my example, result = 3 Now, I would need to add the possibility to count only for B=1; in my example, result should be = 2.

View 9 Replies
View Related
Jul 1, 2009

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

View 4 Replies
View Related
Oct 18, 2011

How do I count unique values in Column A only if the values in Column B match?

A B

1 a

2 a

1 a

4 b

5 b

1 b

The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.

View 5 Replies
View Related
Mar 18, 2013

I've got two columns, one with policy numbers, the other with error codes.

A policy could be in the list more than once, so I'm counting the number of unique policy numbers with the function

=SUM(IF(FREQUENCY(A10:F10000,A10:A10000)>0,1))

I want to count the number of errors that are NOT code 00 or code 21. Because the error codes are saved as text, I'm counting the number of errors with

=SUMPRODUCT((B10:B10000"21")*(B10:B10000"00")*(B10:B10000""))

The problem is that since a policy number could be listed twice, when I count the errors, I might be counting the same policy as an error twice. This means that I could feasibly end up with more errors than I have unique policies, which doesn't work.

What I'd like to do is only count the number of errors that have unique policy numbers. That is, I only want to count an error the first time it is in the list, based off it's policy number. Is that possible?

Here's my sample data: [URL]

View 9 Replies
View Related
Dec 17, 2009

I'm trying to get a forumla to tell me if a filtered column is showing a unique entry.

suppose column A has as follows

A1 - Bob

A2 - Bob

A3 - Sam

A4 - Bob

A5 - Sam

Now, if I filter by ANY column, I want to have a formula that will tell me if the filtered/visible name(s) in column A is only 1 name. Mind you, if the user filters by criteria in a another column, the unique name may show in column A, only once or multiple times depending on how the other column is filtered.

I've tried count, counta, countif and such but doesn't seem to deal with what I am trying to do.

View 9 Replies
View Related
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 ?

View 14 Replies
View Related
Aug 16, 2012

How do I count values in filtered cells?

After I filter for values in Columns(C), I want to count values in Columns(D). When I filter data, I noticed I can manually select the filtered cell range (i.e. Columns(D)) and at the bottom in the Status Bar it will give the correct count. If I cursor over that status bar count it will display "Number of selected cells that contain data." However, I cannot seem to figure out how to get vba to select just the filtered cells selected in that range and count them only and correctly... it instead counts all the values to the last cell in the filtered range.

For example

1) let's say I have a Range("D1:D120') and there are 20 cells containing data in that range.

2) I filter Columns(C) resulting in 10 rows and the rows are 2, 3, 20, 22, 27, 30, 37, 41, 56, 60. Of those 10 rows, there are only 2 cells containing data (let's say cell D20 and cell D37).

3) Now in reality, cells in Range("D2:D60") actually contain 10 cells with values, but since we filtered on columns(C) values... Columns(D) only shows 2 values.

...... here is my problem:

4) If I manually select the filtered range, the status bar "Count" value will correctly sum as "2." But if I code VBA or even "Record a macro code" (obviously incorreclty on my part), it will return a value of 10 for the range of D2:D60 and not on the visible filtered value which is "2."

View 6 Replies
View Related
Mar 20, 2006

Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks?

View 14 Replies
View Related
Oct 24, 2007

=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.

I have attached a worksheet with the example.

In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count.

When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is $1050 in cell C99.

Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.

View 2 Replies
View Related
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.

View 3 Replies
View Related
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

View 1 Replies
View Related
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 ....

View 14 Replies
View Related
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.

View 14 Replies
View Related
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

View 4 Replies
View Related
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)

View 9 Replies
View Related
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.

View 4 Replies
View Related
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.

View 8 Replies
View Related
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).

View 8 Replies
View Related
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.

View 9 Replies
View Related
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

View 10 Replies
View Related
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): ...

View 4 Replies
View Related
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

View 9 Replies
View Related
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.

View 2 Replies
View Related
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.

View 9 Replies
View Related
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).

View 1 Replies
View Related