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?

ADVERTISEMENT

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

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

View 9 Replies View Related

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

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

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

Excel 2013 :: Unique Count In Group SubTotal

Apr 16, 2014

Using Excel 2013.

Is it possible to get a unique count in a group subtotal though the elements in the group may not be unique using Pivot Tables w/o resorting to Power Pivot?

Ex

Group 123
Group 456

Are both members of NorthDivision

If someone is in Group 123 for 9 months, then Group 456 for 3 months, that membership should be reflected in the correct group accordingly.However, that person should only be counted once in the NorthDivision, not twice.

Output:

Group 123 0.75
Group 456 0.25
NorthDivision 1

At first glance, it appears easy, however, what if membership is only total 3 months?Then Group would increment 0.25, but I need NorthDivision to increment 1

View 5 Replies View Related

Can Add A Subtotal Of 3 Columns In A Pivot Table

Nov 22, 2013

I want to get a subtotal of columns B C and D in a pivot table. I have tried to add a calculated item to a pivot table to add columns B C and D. When I try adding a calcuted item I am getting an additional column inserted after columns B C and D. Each additional column has the previous column duplicated. I want a subtotal of column B C and D. I don't want to use the grand total function because I also have columns E through H that I don't want in the subtotal.

How can I get the columns B C and D subtotaled within the pivot table?

View 5 Replies View Related

Get Summery Using Subtotal Or Pivot Table?

Jul 22, 2013

I have certain data and I want to make summery of it,how can we achieve it .can we use subtotal or pivotable

View 1 Replies View Related

Reference To Subtotal In Pivot Table

Aug 11, 2007

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

Count By Unique Entries

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

Pivot Table Subtotal Removal Macro?

Jun 1, 2012

Below is part of my recorder macro for removing subtotal on a specific field:

ActiveSheet.PivotTables("PivotTable2").PivotFields("PRODUCT MANAGER"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)

I am new to coding in a pivot. I hope the no.of "False" depends on no.of "PRODUCT MANAGER" (There are 12 PRODUCT MANAGERs)

But the problem is the no.of PRODUCT MANAGERs vary with data.

So i am stuck on how i can input correct no.of "False" (here 12) inside the array part.

View 1 Replies View Related

Pivot Table - Subtotal Of Negative Values

Feb 5, 2009

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
etc...
Grand Total
Grand Negative Total
Is it possible to add such functionality?

Image for reference:

View 9 Replies View Related

Couting Unique Entries With Second Condition Not Using Pivot

Nov 2, 2009

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.

=SUMPRODUCT((B5:B14="A")/COUNTIF(A5:A14,A5:A14&""))

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

View 11 Replies View Related

Pivot Table Subtotal - Average Of The Fill Rate To Be A Value?

May 1, 2014

I am working on a pivot table and am having issues with our subtotal: Sum / Averages.

The pivot is setup as so:
excel help.jpg

The problem is I need the average column to average the viewable area, and not from the data, so for example G28 should be 55 and not 6. Also, I need the Average of the fill rate to be a value.

View 2 Replies View Related

Pivot Table: Adding A Percentage Field As '% Of Subtotal'

Nov 21, 2007

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

Pivot Table >>Field Settings >Subtotal = Automatic

Sep 18, 2009

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

Count Unique Entries With Criteria?

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

Count Unique Entries In A Range

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

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

Count Unique Entries Within Variable Date Range ..

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

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

View 6 Replies View Related

Count Number Of Rows With Unique Entries In 2 Columns

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

Count Unique Entries Within Variable Date Range

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

Count Unique Text Entries Given A Certain Condition In A Separate Range?

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

Identifying Unique Entries Based On Unique Entries In Another Column?

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

Pivot Table / Chart Not Grouping Same Entries

Feb 4, 2010

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

Clearing Pivot Table Drop Down Entries

Mar 18, 2007

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 3 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

Pivot Table - Stop It Summing Date Entries

Dec 5, 2013

How do I stop a pivot table adding data together?

e.g.

1st Nov -100
1st Nov - 100
2nd Nov - 200
2nd Nov -200

I want this but instead get this:

1st Nov - 200
2nd Nov - 400

I need the pivot to split it out,

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

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

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

ADVERTISEMENT