Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Count Of Unique Entries As The Subtotal Of A Pivot Table

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Count Unique Values In A Pivot Table
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.

View Replies!   View Related
Count Unique Values In Pivot Table
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 Replies!   View Related
Count Of Unique Values In Pivot Table
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 Replies!   View Related
Reference To Subtotal In Pivot Table
I have a pivot Table with region & product wise as shown in the enclosed file. While calculating % of each item in region it is referring to grand total or total of column. Is there any way how to get this % calculated in regard of subtotal region.

View Replies!   View Related
Pivot Table - Subtotal Of Negative Values
I have data in a pivot table that has some positive and negative values. At the end of the Pivot Table, it sums the values for each month and then a grand total.
I would like to also have it show the summation of all negative values per month. So it would be:Jan Total
Jan Negative Total
Feb Total
Feb Negative Total
Grand Total
Grand Negative Total
Is it possible to add such functionality?

Image for reference:

View Replies!   View Related
Pivot Table >>Field Settings >Subtotal = Automatic
I create a Pivot Table in Excel 2003, excel by default puts the field settings for each of the columns to Automatic, creating a Total for each aggreate column, which is very annoying.

I have to manually go in in each field and change field settings > None. for each column, since no copy and paste special function to make all the columns have same subtotal >none.

View Replies!   View Related
Couting Unique Entries With Second Condition Not Using Pivot
Could someone let me know if my suggested formula below will calculate the number of Unique records that represent either an A or a B.


Although it is giving me the right answer here I am not confident it is correct.

View Replies!   View Related
Count By Unique Entries
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,

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 Replies!   View Related
Count Unique Entries In A Range
count unique entries in the Range A1:A10

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

View Replies!   View Related
Count/Sum Unique Values/Entries
Can DCOUNTA be used to only count unique values?

If the above is not possible how would I do the following:


Column H = text1 AND Column B date is between date1 & date2

Count unique values in column C

View Replies!   View Related
Count Unique Entries Within Variable Date Range
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 Replies!   View Related
Count Unique Entries In One Column That Meet Conditions
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.

jones m
jones m
west f
jones m
west f

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 Replies!   View Related
Count Unique Entries Within Variable Date Range ..
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 Replies!   View Related
Count Number Of Rows With Unique Entries In 2 Columns
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 Replies!   View Related
Pivot Table / Chart Not Grouping Same Entries
I have recently noticed a problem with a pivot table and assoicated pivot chart. The table has numerous columns from date, location and one entitled route. The route column has a value which is either a number or 'other'. The formatting of the column has been set to general (although the same problem was present under text). The problem is as follows:

When a new row of data is entered where the route is the same value as previous rows it should be grouped together. However when I go into my pivot table and look at the options for route I find the same value repeated twice. ie a route value of 155 will be listed once at the top and then lower down the list. A temporary fix is to rename all values of 155 to x155 and then back to 155. This then has everything grouped again under one value in the pivot table. As soon as new entries are added though the problem repeats itself. Does anyone know why this might be?

View Replies!   View Related
Clearing Pivot Table Drop Down Entries
I was wondering if there is a simple way of deleting past entries from a drop down list for a pivot table. I have tested my spreadsheet and now want to create a 'blank canvas' for users.

View Replies!   View Related
Pivot - Unique Count
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 Replies!   View Related
Is There A Way To Get A Unique Count In A Pivot
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 Replies!   View Related
Count Pivot Fields In Pivot Table
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
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 Replies!   View Related
Pivot Table And Counting Unique Fields
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;


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


red 4
blue 3
Blue 2
green 2

View Replies!   View Related
Pivot Table: Adding A Percentage Field As '% Of Subtotal'
In the attached Excel file, there is a pivot table.

In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total".

The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).

View Replies!   View Related
Listing Unique Values And Summing W/o A Pivot Table
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 Replies!   View Related
Count In Pivot Table
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 Replies!   View Related
Pivot Table With Count
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 Replies!   View Related
Pivot Table Count Function
I'm working with Pivot tables, and have run into a issue with counting unique cells, when there are multiple like cells. Here is an example


I need to know how many accounts there are (answer=2)
When I use Pivot table, Count of Accounts, it returns a count of 3.

View Replies!   View Related
Pivot Table Count Only 1 Query
the attachment is a sample spreadsheet of what i am trying to do. I would like to create a pivot table so that for each site I can list all of the organic suites individually. Then show a count of only 1 for each site, even when there are several counts for each site. And then for this to be related to a cost of each of the suites so that I can get a total cost for each site.


site name

organic suites suite cost
o1 x
o2 y
o3 z
Total A

View Replies!   View Related
Count By Categories Within Pivot Chart/Table
I can not get my Pivot Chart to count, sort and categorized my data. I have included a sample file for your viewing. I basically want the data in cells C2:E18 counted, sorted and totaled by categories and locale. I thought the pivot charting was simple, but I'm having quite a challenge with this one. The pivot chart data should reflect as my sample indicates in cells E24:K27

View Replies!   View Related
Pivot Table (count As A Single Fail Against The Associate)
I have a source data tab laid out like this:

QC Date.......Policy#......Associate.......Pass/Fail......1stError.....2ndError.....3rdError

QC'ers can enter up to 3 different error types committed on a single failed policy, thus the reason for 3 error fields, even though all 3 fields offer the same value list selections (ie ErrorTypeA, ErrorTypeB, ErrorTypeC......). A policy, whether it has one error or three errors assigned, should only count as a single fail against the associate.

My problem comes into play when attempting to pivot the data. I would ideally like to see the pivot table in this format:

(filter)QC Date

Where the "count" is the total number of occurances of that error type across all three error fields - 1stError, 2ndError, 3rdError.

But since 1st/2nd/3rd error fields are different columns, I am unable to do a single count.

My pivot ends up looking something like this:

Then repeated for the 2ndError and 3rdError fields.

View Replies!   View Related
Pivot Table Grand Total And Sum/count
I started a pivot table for our budget and on the left side I have the account names and about 4 columns of applicable account codes to which I turn on and off when needed. On the right side I have all the budget numbers divided by quarter and halfs. On the right side, I can drop any list of numbers and it does the sum but for some reason, when I drop my 4th quarter numbers, it gives me a count and not sum. How do I change it to sum?

Second problem: I have grand totals number going down the columns meaning I have a sum of all my 1st Quarter numbers but I do not have them going across each line on row. How do I add that?

Third problem: I forgot a list of account codes to be put on the left, how can I add it?

View Replies!   View Related
Macro Resetting Pivot Table Field To Count
I've recorded a macro that clears a worksheet, fetches data from an Access Table and then creates a new pivot table. The Pivot Table Fields are summarized by "sum" and this worked the first few cycles for the macro. Now it is returning the Pivot Table Fields summarized by "count". Can an option be inserted into the existing code to specify "sum"? Here is the

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'raw data'!R1C1:R205C12").CreatePivotTable TableDestination:="", TableName _
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable14").SmallGrid = False
ActiveSheet.PivotTables("PivotTable14").AddFields RowFields:=Array("Name", _
With ActiveSheet.PivotTables("PivotTable14").PivotFields( _
.Orientation = xlDataField
.Position = 1
End With..................................

View Replies!   View Related
Unique List Would Grab All Unique Entries
I have a data table that repeats as follows:


From the data above I need to make a new unqie list that would grab all unique entries.

I am trying to use the following guide:

Get stuck on "Listing A:

View Replies!   View Related
Create Pivot Table: Cannot Open Pivot Table Source File
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.

View Replies!   View Related
Pivot Table Query: Make A Pivot Table To Summarise The Data
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.

View Replies!   View Related
Subtotal Non-Unique Items By Employee
I have a list of sales associates by items by manufacturer. I need to count how many manufacturers are represented by each associate. It might look something like this:

Smith, John Brand A Beer 12 oz Beer Industries, Inc.
Smith, John Brand A Beer 8 oz Beer Industries, Inc.
Smith, John Brand B Vodka 700ml Vodka Industries
Jacobs, Sally Brand C Wine 1989 Wine Makers Co
Jacobs, Sally Brand C Wine 1979 Wine Makers, Inc.
Jacobs, Sally Brand D Wine 1908 Old Wine Makers, Ltd.
Jacobs, Sally Brand E Beer Six Pack Fun Beer Co.

So the totals would be:

Smith, John Total # of Manufacturers: 2
Jacobs, Sally Total # of Manufacturers: 3

So the formula ignores the duplication in employee names and manufacturer names.

Is this possible? I'm thinking something like =sumproduct((Range=AssociateName)*(sum(1/countif(ManufacturerRange, ManufacturerRange)))

View Replies!   View Related
Change The Date On One Of The Pivot Table And Pivot Table Match
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.

View Replies!   View Related
Average Of Pivot Subtotal
I have a pivot table with the following row data in this order: Customer - Order# - Part#. My data field is Sum of Total, ie the sum of the total $ paid for the parts by order by customer. The pivot table provides me with a total by order, ie total $ paid for parts by order, and sum of customer, ie total $ paid for all parts for all orders. If I change the sum of customer to average of customer it provides me with average $ per part but I want average $ per order. I cannot find a way to average the subtotal by customer, ie what is the average order $.

View Replies!   View Related
Pivot: Percentage Of Subtotal And Total
I have created an old fashioned pivot table. I would like to have it show percentage of subtotal and show percentage of Grand Total, to 3 decimal places. See REPORT tab. In column D of attachment, I have entered in text what I would like to see in this column for INCOME, EXPENSE, AND SPENDING pivot table.

View Replies!   View Related
SubTotal Count(2)
I a formula in one of my cells so that when the data is filtered it will add up all the cells that have a yes in them. However I'm getting an error. If I use the countif formula it still adds all the cells with yes that are not in the filter selection.

View Replies!   View Related
Count All Unique Values (text) + Definition Of UNIQUE :D
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 Replies!   View Related
No Count Formulas By Subtotal
i want to count only nonblanks cell and based other threat i try modified the formula, but this doesnt count. how can i defined this forumula to count only nonblanks cells?

View Replies!   View Related
Subtotal Of Data Table
My data sheet is attached for reference. The yellow cells show the end result I'm aiming for.

After each change in "NAME", I have inserted 2 blank rows. In the first blank row, column A, I want the text "ENDTRNS". In the second blank row:

column A - "TRNS"
column B - "Bill"
column C - reference the cell below
column D - "Accounts Payable"
column E - reference the cell below
column F - sum the next cell down to the last cell before the next blank row (sum range size will vary)
column G - reference the cell below
column H - reference the cell to the left
column I - "Y"

Then search out the next set of 2 blank rows and repeat, all the way down to the last set of blank rows.

View Replies!   View Related
Count Formula: Count Total Entries In Columns
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8

View Replies!   View Related
Change Sum To Count In Subtotal
I am working with some data that needs to be subtotalled. All the fields are subtotalled by sum, although 1 column needs to be count.

I was planning to do the subtotal (by sum) for all the data and then get the macro to go and change that 1 column to count.

My problem is that this will be a weekly report and the number of rows will vary, so how can I changet the sum to count without hardcoding the cell ref's?

View Replies!   View Related
Delete Unique Entries
I was wondering if there might be a better way to write this macro. What it does is clears unique items from a Range( leaves duplicates ) I've looked all over the net I can find all kinds of function and subs to remove duplicates but haven't been able to find anything that just removes single entries. I"ll bet there's a more elegant way to write this maybe using a Collection or a Dictionary.

Sub Dummy()
Dim MP1_Rnge As Range
Set MP1_Rnge = Range("A1:A100")
For Each Cell In MP1_Rnge
If Not IsEmpty(Cell) Then
If Cell.Row = 1 Then..........

View Replies!   View Related
Identifying Unique Entries
I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher).

So, for example, if i had the following values in column C:One

I would want the following values in column E:One

View Replies!   View Related
Sum Unique Entries
I have attached a sample workbook to try and explain what I'm after.

Basically I want to sum the amount of occasions a person has had checks and each occasion could have 1,2 or 3 individual checks.

View Replies!   View Related
Choosing Unique Entries
I'm trying to write an Excel program to calculate golf handicaps. In column A, I have 20 unique dates. In column B, I have corresponding 20 scores. In column C, I'm trying to create a corresponding notational mark, "*", indicating the 10 lowest scores that are also the oldest. However, I'm running into problems as I may have duplicate scores with different dates, i.e. 3/22/06 will have a score of 85 and will be the 10th lowest score, but 4/1/06 also has a score of 85.

View Replies!   View Related
Extract Unique Entries
I have a list with many names, several of them appearing more than once. I would like to extract the unique entries an then make the list of them.

I can do that using the filter. But can I do it with formulas ? (In order to, whaterever change is made to the first list, get the extracted one immediately updated).

View Replies!   View Related
Counting Unique Entries ...
Couting Unique Entries

I've included a sample worksheet ...

View Replies!   View Related
Filtering Unique Entries
I have this 2 columns, A(region) and B(location), and I need a macro to filter the unique entries in Columns A and return both the unique values in A and their corresponding values in B. I've attached a file on it with sheet 1 having the data to be worked on and sheet 2 being the output that I require.

View Replies!   View Related
Copyright 2005-08, All rights reserved