# Count Number Of Unique Values Based On Multiple Conditions

Feb 22, 2011

I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.

In excel is best illustrated what I mean!

In total, 5267 lines and need to do this with all. You can do this in excel?

## Count Unique Values With Multiple Conditions Array Method

Apr 28, 2014

I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.

I've attached the XL file for your review of the project.

=SUM(IF(FREQUENCY(IF(('TX1'!\$B\$2:\$B\$15=B2)*('TX1'!\$C\$2:\$C\$15=C2)*('TX1'!\$D\$2:\$D\$15=D2),MATCH('TX1'!\$A\$2:\$A\$15,'TX1'!\$A\$2:\$A\$15,0)),MATCH('TX1'!\$A\$2:\$A\$15,'TX1'!\$A\$2:\$A\$15,0))>0,1))

## Count Unique Occurrences Based On Multiple Conditions In Another Cells

Sep 19, 2013

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet : Example email report.xlsx

The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

## How To Count The Number Of Unique Values In A List, Based On Another Value

Jul 27, 2009

I need to count the unique values in a list based on another value. i.e.

AB ___ Jones
AB ___ Smith
BC ___ Henry
AB ___ Jones
BC ___ Brown
BC ___ Henry
AB ___ Tomkins

For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:

=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))

but haven't been able to get it to work.

## Count Unique Logs With Multiple Conditions Of Multiple Sheets

Oct 8, 2007

I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.

This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.

The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.

Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))

Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.

2. I don't know how to make it work across several sheets.

This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.

SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))

## Counting Unique Values With Multiple Conditions

May 7, 2014

I need counting unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.

The formula I have is:
SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D<=Z4,G:G)))),G:G),1))

The goal is to count all of the unique value in column G:G that meet certain criteria (have ending times greater than Z3 and beginning times less than Z4, etc.). Once I get the formula to work, I'll simply drag it down to have it calculate for similar lightly changing criteria (like different beginning and ending intervals). However, the formula doesn't seem to be working. It's only giving me zero values (I have "ctrl-shift-entered").

## Counting Unique Values With Multiple Conditions

May 7, 2014

I need to count unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.

The formula I have is:
SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D

## Lookup Unique Based On Multiple Conditions ...

Jan 2, 2009

I am looking for a solution other than using an advanced data filter for unique records only.

I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B).

Group A only includes records with value ="A" in the checksheet column.
Group B includes records with value <>"A" in the checksheet column.

Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated?

SEE ATTACHED:

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

## Advanced Filters - Having Multiple Filters And Conditions (Unique Count)

Jun 7, 2006

I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.

## 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 Number Of Unique Values After 1st Part Of Cell Matches?

Aug 8, 2014

Have numerous values in Col A. Col E extracts a list of unique values from that column.

In Col C, the Col A value has had characters added to it.

Need a formula to count the number of unique values from Col C which contain the same prefix from Col A, and place the result in Col F.

A sample workdook is attached with the desired result shown and highlighted in yellow.

## Count Unique Values Based On Another Column

Jul 1, 2009

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

## 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 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 Occurrences Based On Unique Values In Another Column

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]

## 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 Based On Duplicate Status In Another Column On Filtered Data

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

## Record Duplicate Values Based On Multiple Conditions Only

May 1, 2014

I am trying to pick out certain bits of information from the below "example" set of data:

A
1

A
1

B
1

C
1

[Code] .......

My aim is to record the letters that are recorded against both numbers (note: in my data there are more than 2 sets of numbers). For the example above the solution would be:

A
1,2

B
1,2

...because these two letters appear against both 1 and 2.

There are some letters that are duplicated against the same number which is making it hard for me to work out. I don't care if the same letter appears against the same number, I just would like to know instances when a letter appears with a different number, and if possible what that number is.

## Count Unique Entries In One Column That Meet Conditions

Jan 17, 2010

I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]

The situation:

I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.

namegender
jones m
martinf
smithf
collinsf
wilsonm
jones m
martinf
hughesm
wilsonm
martinm
smithf
west f
jones m
west f
martinm

The challenge:

In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!

The correct result: ...

## Combine Multiple Records Onto 1 Row Based On Unique Number In Column

May 2, 2008

I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel.
I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.

What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:

BEFORE
HH Acct
1 1234
1 2345
1 3456
1 4567
2 9876
2 8765
3 1113
4 5556
4 4447
4 3335

AFTER
HH Acct1 Acct2 Acct3 Acct4 Acct5 Acct6 Acct7
1 1234 2345 3456 4567
2 9876 8765 8765
3 1113
4 5556 4447 3335

## Counting Unique / Different Values Based On Multiple Criteria?

Jan 21, 2014

I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).

Program
Clinic Title
Start Date

[Code].....

formula that will automatically give me that answer?

## Find Common Values Across Multiple Sheets Based On Unique ID

Oct 24, 2013

The old thread is here: [URL] ....

There are three sheets in the workbook, Project, Tasks and Details and the expected resulting sheets are RESULT, In_Tasks_but_NOT_in_Projects and In_Details_but_NOT_in_Projects .

But now what I am looking for:

1. Copy the Projects data as is in the RESULT sheet.

2. Then in the Tasks sheet, if the ID matches paste the matching rows under the data from Projects (as in the result sheet with Orange colour)

3. If the ID is present in Tasks but NOT in Projects then copy it into the In_Tasks_but_NOT_in_Projects sheet.

4. Then If the ID and the Name in the Details tab matches with the data in the RESULT sheet then paste it under the ID and Name (as in the result sheet with Green colour)

5. If the ID does not match the ID in the results sheet then copy that row into the In_Details_but_NOT_in_Projects sheet.

The result of the current macro that RHCPgergo worked with are in the last sheet.

The formatting and colour of the rows doesn't matter, it is more of nice to have.

## 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 Unique Instances Of Date While Filtering Based Upon Date And ID Number

Aug 14, 2014

I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.

I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.

I've attached an example work book and I would like the result in H2.

Example Workbook.xlsx‎

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

## Write Unique Values Under Certain Conditions?

Sep 30, 2013

What I am trying to do is create a list of unique values under certain conditions. I will try to explain it as best as I can.Imagine we have four columns:

Column A contains a product, so for example Apples, Bananas, Oranges...
Column B contains names, so for example Peter, Carl, Linda...
Column C contains stores, so for example Supermarket, Cornershop, ...
Column D contains a number, so for example 1, 2, 3...

Now let's say I want to see who bought apples in the supermarket, I would like to query kind of like this:

For all rows where column a = "Apples" and column c = "Supermarket", write me Column b value and expect something like

Peter
Linda

if only these two bought apples in the supermarket.

But I need to make sure its only unique people, so if Peter bought apples at the supermarket twice, I do not want to see

Peter
Linda
Peter

Now the next step would be to see what was the maximum amount of apples that peter has ever bought at the supermarket, so Im thinking of something like:

For all rows where column a = "Apples" and column b = "Peter" and column c = "Supermarket", find me the maximum value that is ever displayed in Column D.

Like I said, I'm totally new to VBA so I am not really sure how to get started with this, but what I do know is that doing this with if statements would take me forever, so I want to give vba a try.

## Count The Number Of Multiple Values In A Single Cell

Jan 28, 2010

formula that will return the number of "x"s or "o"s within the same cell.

The cell has values that are formatted in multiple ways for example: PXX--XXP, --XO, OXX--.

I want the formula to return 4 if the cells has PXX--XXP or 3 if the cells has --OXX etc.

## Count Unique Cells Based On Ajacent Cells Having Values

Jan 6, 2008

I have the following data in 1 of the tabs.

[TABLE]
ClassSequenceAB
10SE132422
20SE23212
20SE321
20SE23425
10SE332455
15SE132412
10SE234
[/TABLE]

I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:

[TABLE]
ClassAB
1032
1511
2012
[/TABLE]
Can this be achieved through a formula?

## Multiple Criteria And SUMPRODUCT (count The Number Of Rows That Have Values Greater Than 10/01/2008 In Either Of Two Fields)

Jan 23, 2009

I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.