Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Unique Values in Pivot table
I'm making a pivot table that counts the number of accounts#'s. Unfortunately
the account#'s are sometimes duplicated. Without altering the original data, is there a way I can do something in my pivot table so that it only counts unique account numbers and discards the dupes? Thanks. IE: Name #of new accounts Bob 6 Joe 3 Sam 2 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Unique Values in Pivot table
In pivot table select the value.
Right click | field setting | options | show data as : index | ok On Jan 31, 1:26*am, Craig860 wrote: I'm making a pivot table that counts the number of accounts#'s. Unfortunately the account#'s are sometimes duplicated. Without altering the original data, is there a way I can do something in my pivot table so that it only counts unique account numbers and discards the dupes? Thanks. IE: * Name * * * #of new accounts Bob * * * * * * 6 Joe * * * * * * 3 Sam * * * * * 2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Unique Values in Pivot table
That didn't work as expected. I got a really odd number like 0.914170838
the sum should be around 600 or 700. "muddan madhu" wrote: In pivot table select the value. Right click | field setting | options | show data as : index | ok On Jan 31, 1:26 am, Craig860 wrote: I'm making a pivot table that counts the number of accounts#'s. Unfortunately the account#'s are sometimes duplicated. Without altering the original data, is there a way I can do something in my pivot table so that it only counts unique account numbers and discards the dupes? Thanks. IE: Name #of new accounts Bob 6 Joe 3 Sam 2 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Unique Values in Pivot table
You could use a formula like
=COUNTA(A:A) - 3 where the account numbers are in the first column of your pivot table. Change the 3 depending on your subtotal settings... HTH, Bernie MS Excel MVP "Craig860" wrote in message ... I'm making a pivot table that counts the number of accounts#'s. Unfortunately the account#'s are sometimes duplicated. Without altering the original data, is there a way I can do something in my pivot table so that it only counts unique account numbers and discards the dupes? Thanks. IE: Name #of new accounts Bob 6 Joe 3 Sam 2 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Unique Values in Pivot table
Sorry I don't follow what you want me to do.
In my pivot I got the account name in column A. in my data I have Count of Acct# there may be 1000 account numbers but I only wan't to count the unique numbers which should reduce it to about 500. "Bernie Deitrick" wrote: You could use a formula like =COUNTA(A:A) - 3 where the account numbers are in the first column of your pivot table. Change the 3 depending on your subtotal settings... HTH, Bernie MS Excel MVP "Craig860" wrote in message ... I'm making a pivot table that counts the number of accounts#'s. Unfortunately the account#'s are sometimes duplicated. Without altering the original data, is there a way I can do something in my pivot table so that it only counts unique account numbers and discards the dupes? Thanks. IE: Name #of new accounts Bob 6 Joe 3 Sam 2 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Unique Values in Pivot table
Craig,
Excel will only put a values once into column A of your pivot table, so using the formula COUNTA(A:A) - X will report the number of unique values. If an account number seems to appear more than once, then it is being entered differently somehow in the data tabel, as Excel is interpreting those instances as being different. Perhaps you have an extra space, an added digit, a dash, or some other misspelling in your account numbers. HTH, Bernie MS Excel MVP "Craig860" wrote in message ... Sorry I don't follow what you want me to do. In my pivot I got the account name in column A. in my data I have Count of Acct# there may be 1000 account numbers but I only wan't to count the unique numbers which should reduce it to about 500. "Bernie Deitrick" wrote: You could use a formula like =COUNTA(A:A) - 3 where the account numbers are in the first column of your pivot table. Change the 3 depending on your subtotal settings... HTH, Bernie MS Excel MVP "Craig860" wrote in message ... I'm making a pivot table that counts the number of accounts#'s. Unfortunately the account#'s are sometimes duplicated. Without altering the original data, is there a way I can do something in my pivot table so that it only counts unique account numbers and discards the dupes? Thanks. IE: Name #of new accounts Bob 6 Joe 3 Sam 2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Pivot Table Count | Excel Discussion (Misc queries) | |||
Unique count in Pivot Table | Excel Worksheet Functions | |||
Count Unique using Pivot Table | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Charts and Charting in Excel |