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.
View 9 Replies View RelatedI 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.
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 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?
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...
View 9 Replies View RelatedI'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 RelatedI 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 RelatedI 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.
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
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 RelatedHow 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
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.
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 RelatedI 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;..............
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
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.
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.
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
I have the below code, in a cell but I am trying to input this into a macro, so the only thing that shows in the cell is the formula.
The formula I have in the cell is:
I have this formula that works perfectly fine for what I am trying to do (from another post) but I am wanting to transfer this into VBA. It is an array formula so I'm not sure if this can be done.
How can I convert this, into R1C1 code or any way possible. I would put it within a WITH RANGE statement.
aaa.xls
how to count lines in the same order
for exp for this attachment file is 7 lines
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 RelatedI am attaching the sheet : Uniquecount.xls
Need to do this using formulas and without needing to add any new columns.
Need a formula to lookup the details sheet (for easyness i just added it as a new tab here) and count the unique names in a dept and display that in the summary sheet/tab.
Want the formula to be applied to the summary to go and lookup/count from the other sheet.
Have shown the expected results in the col "total unique heads".
Any formula to count only unique values in particular column
E.g.
1
2
3
1
5
1
6
1
answer should be 5
Col A Col B
163401 1
163401 1
163401 0
163402 1
163402 0
163402 0
GOAL -I want to be able to count Col B only once for the same set of records in COL A.
Based on Distinct criteria on COL A, I need to be able to count COL B. The count should be 1 for 163401 and not 2. Similarly for 163402 the count should be 1
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
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 ....
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.
