# 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

View 9 Replies
ADVERTISEMENT
Feb 24, 2007

I am hoping this can be done with formulae. Starting at C7 and continuing down the C column there is a list of names which could potentially run from C7 to C5000. This list of names will contain duplicates. For each name there is a corresponding 'reason' in the F column which will contain the word 'Truancy' or 'Late'.

I need a formula that can count the number of UNIQUE names in the C column which correspond to the word 'Truancy' or 'Late' in the E column.

An example,

[Name].....................[Reason]

C7 ...........................E7

John Potts.................Truancy 2

Matt Jones................Truancy 10

John Potts.................Truancy 4

Matt Jones ...............Late AM

Pete Burns................Late PM

Pete Burns ...............Late Both

Steve Lopez..............Truancy 6

Count of unique names with the word Truancy in the corresponding E column = 3 [John Potts has 2 instances of the word truancy in column E but this is only counted once]

Count of unique name with the Word Late in the corresponding E column = 2 [Pete Burns has 2 instances of the word late in the E column but this is only counted once].

I have also included a sample workbook.

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

View 9 Replies
View Related
Sep 5, 2007

count unique entries in the Range A1:A10

i have data which repeats but i ant to count only unique entries?

View 9 Replies
View Related
Apr 27, 2009

I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.

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

View 6 Replies
View Related
Jan 28, 2010

I have attached an example of what i am trying to do. I can work out how to calculate unique entries by putting in a formula under a pivot table, but is it possible to select an option in the pivot table settting which will give this result?

View 2 Replies
View Related
Oct 9, 2006

I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.

I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).

I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.

All the cust numbers are unique so would I be able to use a wildcard?

View 4 Replies
View Related
Jan 26, 2008

Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.

Goal: create a count of unique entries within a defined variable date range

I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.

I've been attempting to use the DCOUNT function but I'm not getting the correct result.

Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.

It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?

View 9 Replies
View Related
Aug 11, 2014

I need to count the number of unique text values in the first column given a condition in the 2nd column. For example, the formula that I'm looking for should give a result of "3" because it is an SME, and there are 3 unique companies that are classified as SMEs.

Company Name

Company Type

Company A

SME

Company B

SME

(I cant seem to make the tables visible but Company A and Company D right below the header should be in one cell) This should still yield a value of "3" despite having Company A and D in the same cell.

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

View 5 Replies
View Related
May 29, 2014

I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.

Example List

Fruit

Color

Apple

[Code]....

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
Sep 27, 2012

I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?

View 4 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
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
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
Jul 1, 2009

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

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