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.

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

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.

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.

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.

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.

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?

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.

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.

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 ?

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?

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.

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

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

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.

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?

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.

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.

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

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.

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?