Count Unique Values In A Pivot Table

Mar 3, 2004

I have a spreadsheet with several records for each person's name.
I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

When I set up a basic Pivot, it counts each occurence of the person's name.

ADVERTISEMENT

Count Unique Values In Pivot Table

Feb 28, 2008

I have a worksheet with a list of employees and the workgroup they belong to, along with other data like manager, start dates, etc. Recently a couple of the workgroups were duplicated (change in managers), so these employees are showing up on two rows even though the workgroup has the same name. The only differences in the two rows are the workgroup effective start and end dates. I need to be able to count, in a pivot table, the number of unique employee/workgroup combinations there are per workgroup. I can add columns to the sheet, but it's a dynamic set of data that will grow each time it's refreshed...

View 9 Replies View Related

Count Of Unique Values In Pivot Table

Jun 19, 2008

I'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.

View 5 Replies View Related

Count Unique Values By Month In Pivot Table?

May 16, 2013

I have data that has a customer name and the month that they were taken care of" (of course not spaces

Customer Month
Smith January
Smith January
Mark January
Suzy January
Smith February
Mark February

I want a pivot table to say:

Month #customers
January 3
February 2

but when I do the count it adds the total number of customer names and gives (it is counting smith twice in January

Month #customers
January 4
February 2

I was reading something about doing the count (so it shows 4) and then doing an index within the count but I keep just getting 1 as the value (for both months).

I tried doing a formula in the document that said =IF(ISERROR(MATCH(A2,$A$1:A15,0)),1,0). basically looking to see if the name is above the location I am looking at and if it is to put a 0 if not put 1 and then do a sum of that column....trouble is that when a customer has orders on more than one month, the second month is coming up with a 0 instead of a 1 (and this happens quite often).

I am not sure what to do. if the customer shows up in more than one month I want them to count towards the unique count of both january and february...we look at each month separately.

View 3 Replies View Related

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

View 2 Replies View Related

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

View 4 Replies View Related

How To Count Unique Customers Using Pivot Table

Oct 7, 2011

I have the following data:

TRX_IDQTYCUST_ID892486021-67.991623230892486140-35.411623230892486378-29.271623230892486515-60
.711623230892488006-69.081623230892488082-43.261623230892486668-31.091623230892486794-27
.0562118898892546057-42.829337675892487417-19.331623230892487513-33.251623230892488890-33
.61719407892543537-13.891623142892544321-11.021623142892487602-18.921623230

Would like to know the number of unique customers (CUST_ID) have purchases and the sum of those purchases and how many transactions:

CUST_ID Sum of QTY Count of Transactions
50 $2000 1500

I cant seem to find how to do unique Cust using a pivot table - it just gives me a count of cust which is the same number as the number of transactions.

View 5 Replies View Related

Pivot Table - Count Of Unique Value Not Total?

Feb 9, 2012

The pivot table has only two columns, the first is the identification number and the second is the count of the identification number. I am trying to get a count of the number of identification numbers, not how many times it was entered in the spreadsheet (some numbers are entered more than once on different days). It currently looks like:

Column A Column B
00000001 1
00000002 1
00000003 2
00000004 1
00000005 3
Grand Total 8

I'm trying to arrive at 5 for the answer, so that each number is only counted once even if used more than once.

View 3 Replies View Related

Unique Item Count In Pivot Table?

Jun 29, 2012

I have 2 columns. One with a store number. The other with an item. I need to get the count of each item for each store. So:

Store, Item
1, Cashier 1
1, Cashier 2
1, Cashier 2
1, Photo PC 1
1, Cashier 1
1, Cashier 2
2, Photo PC 1
2, Cashier 1
2, Cashier 2
2, Cashier 1
2, Photo PC 2
2, Photo PC 2
2, DriveThru 1
3, Cashier 1
3, Photo PC 1
3, Photo PC 1
3, Photo PC 2

I would like to get to

Store, Cashier 1, Cashier 2, Photo PC 1, Photo PC 2,
1, 2, 3, 1, 0
2, 2, 1, 1, 2
etc....

How can this be done?

View 3 Replies View Related

Count Of Unique Entries As The Subtotal Of A Pivot Table

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

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.

View 3 Replies View Related

Listing Unique Values And Summing W/o A Pivot Table

May 27, 2009

I have a set of data that I'm trying to identify the unique values in a column and then sum the related quantites against each of those values:

View 14 Replies View Related

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.

View 9 Replies View Related

Pivot Table Not Counting Values (but Does Count Based On Labels)

Jan 24, 2014

I have column in my table that calculates time in hours between now and ticket open date (ticket open date in B1)
so, in C1 i have following formula:

=(now()-B1)*24

Results are in hours, such as 15, 25, 32, 65 etc

In pivot table, I would like to see how many tickets with values of less than 24 or more than 48, etc

My available columns are: Region, Ticket#, AgingOpenHr(this new column).

I tried it different ways, and I cannot get it Less Than and Greater Than work under Values, works if I select grater than or less than in Label, but label does not produce consistent numbers.

View 1 Replies View Related

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 ?

View 14 Replies View Related

Pivot - Unique Count

Jun 14, 2009

I want to have a unique count field in my pivot table. I already read (via forum search) that this can only be achieved by adding an extra column to the database.

The problem is: I have no clue which formula to use for this column. Please have a look at the attached picture. Can someone provide me with a formula for this example?

View 14 Replies View Related

Is There A Way To Get A Unique Count In A Pivot

Dec 4, 2008

I have a pivot that shows Patients, Invoices and charges. I want to throw Patient, Inv # and charge amount in the row area and put PatientId in the Page area as a count. It works fine except it double counts patients with more than one invoice...I want a unique patient count.

View 9 Replies View Related

Count Pivot Fields In Pivot Table

May 2, 2007

I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;

Sub RemoveGhostPivotItems()
Dim ghost As PivotItem
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
For Count = 1 To 10
On Error Resume Next
For Each ghost In pt.PivotFields(Count).PivotItems
ghost.Delete
Next ghost
Next Count
pt.ManualUpdate = False
End Sub

My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............

View 2 Replies View Related

Pivot Table And Counting Unique Fields

Feb 9, 2008

Found what it think is the correct methodology (Pivot table) for doing this, but I'm struggling to apply it.

I have a very simple excel 2007 document that has one column with multiple rows;

blue
Blue
green
red
red
green
Green
blue
red
Blue
blue
Bleue
Rouge
red
Bleue

I would like the easiest way to count the total of each and sort in highest first,

e.g.

red 4
blue 3
Blue 2
green 2

View 9 Replies View Related

Macro To Give Count Of Unique Values After Comparing Comma Separated Values?

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

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.

View 2 Replies View Related

Sum Of Unique Values Using VBA Pivot

Nov 9, 2011

How to use Pivot table and its features in VBA. Lets say i have a data as shown below.

Col ACol BDataVolumeAAA10BBB20CCC30CCC40CCC50CCC60AAA70AAA80BBB90

I would Like to have a VBA code using Pivot where Unique values from Column A is taken and its sum of the volume in it (as shown below).

Col CCol DDataVolumeAAA160BBB110CCC180

View 5 Replies View Related

Excel 2007 :: Extracting All Unique Items From Pivot Table

Feb 20, 2012

I am looking for a way to extract all of the unique items in a Pivot Table. For example, I have a Pivot Table that has category A items and category B items. There may be several B items to one A. For example:

A0
B1
B2
B3A1
B3
B4
B5

Now, what I need is a list that looks like this:

A0 - B1
A0 - B2
A0 - B3
A1 - B3
A1 - B4
A1 - B5

Formatted so that those are two columns. However, in Excel 2007, I cannot set the option to have it repeat the A series items in the Pivot table. Is there some easier way to do this?

View 2 Replies View Related

Unique Values In Pivot Tables

Jul 19, 2005

I have a list of purchase order line items. Each Purchase order could have multiple line items, so the list could have many occurances of the same purchase order number. I have designed a pivot table which summaries line items by ordering area (one of the fields in the list).

This works fine but what I would also like to check is the number of purchase orders by area. How can I acheive this? I would prefer not to use any helper columns but will adapt my application if this is needed.

View 8 Replies View Related

Pivot Table Layout - Generate List Of Unique Names And Their Associated Phone Numbers

Mar 18, 2013

I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.

I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.

But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.

View 7 Replies View Related

How To Count Only Unique Values

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

Count Unique Values ....

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

Count Unique Values

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

How To Count Only Unique Values

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

Count All Unique Values

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

Count Unique Values, For Specific Values

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

Pivot Table Has Both Count And Sum In It?

Jan 3, 2013

how I stop the following code from producing both the sum and count of the data field.

Set Pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), TableName:="PivotTable1")
With Pt

[Code]....

I think the count part (not sure of correct nomenclature) is automatically being generated by the create pivot method and I am not sure how to switch it off.

View 2 Replies View Related

Count In Pivot Table

Jul 11, 2007

I have created a pivot table from a spreadsheet that had around 27 rows for each employee (i.e: each paycheck the employee received). The pivot table turned out great, but I need to know how to make it count how many employees are in each department and show it in the table.

View 9 Replies View Related

Pivot Table With Count

Sep 5, 2007

My requiremntis to get the number of count for the specific pivot data column.

In the EXCEL I am selecting "reason" and "reason_details" as the " range" and creating a Pivot table based on it.

The resulting PT is not giving me count for each set.

In the attached Excel. I have DATA sheet and PT sheet ( whih is created by Pivot ) and

Required PT. My desired requirement is in the Worksheet 'REQUIRED PT".

View 3 Replies View Related

Find And Count Unique Values

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

Count Unique And Specific Values

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

Count Unique Values Between Two Dates?

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

Count Unique Values With Critera

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

Count Of Unique Values With Duplicates

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

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

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.

View 5 Replies View Related

ADVERTISEMENT