Home |
Search |
Today's Posts |
#1
|
|||
|
|||
counting in pivot table
I have the data source like below:
product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 and my pivot table like below: customer product total A 1 1000 2 1000 A Count 2 B 1 500 3 200 B Count 2 How can I get the number of customer = 2 (A, B)? and number of product = 3 (1, 2, 3)? It seems to me that the count on pivot table only count the number of row of that data. Can I get this count from pivot table or other excel formula? |
#2
|
|||
|
|||
One way ..
Assuming this table is in Sheet1, cols A to C data from row2 down product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 In Sheet2 ------------- With the labels in A1:B1 : UniqueProd, UniqueCust Put in A2: =SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100))) Array-enter the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER Copy A2 across to B2 A2 and B2 will return the number of unique items in col A and B in Sheet1 Adjust the ranges to suit -- Rgds Max xl 97 --- GMT+8, 1 22' N 103 45' E xdemechanik <atyahoo<dotcom ---- "Marisa" wrote in message ... I have the data source like below: product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 and my pivot table like below: customer product total A 1 1000 2 1000 A Count 2 B 1 500 3 200 B Count 2 How can I get the number of customer = 2 (A, B)? and number of product = 3 (1, 2, 3)? It seems to me that the count on pivot table only count the number of row of that data. Can I get this count from pivot table or other excel formula? |
#3
|
|||
|
|||
That's great, even though I don't understand how this formula work :)
Thanks a lot! Marisa "Max" wrote: One way .. Assuming this table is in Sheet1, cols A to C data from row2 down product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 In Sheet2 ------------- With the labels in A1:B1 : UniqueProd, UniqueCust Put in A2: =SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100))) Array-enter the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER Copy A2 across to B2 A2 and B2 will return the number of unique items in col A and B in Sheet1 Adjust the ranges to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Marisa" wrote in message ... I have the data source like below: product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 and my pivot table like below: customer product total A 1 1000 2 1000 A Count 2 B 1 500 3 200 B Count 2 How can I get the number of customer = 2 (A, B)? and number of product = 3 (1, 2, 3)? It seems to me that the count on pivot table only count the number of row of that data. Can I get this count from pivot table or other excel formula? |
#4
|
|||
|
|||
You're welcome!
-- Rgds Max xl 97 -- GMT+8, 1 22' N 103 45' E xdemechanik <atyahoo<dotcom --- "Marisa" wrote in message ... That's great, even though I don't understand how this formula work :) Thanks a lot! Marisa |
#5
|
|||
|
|||
Hi Max,
I'm also interested to know how to construct ur prev. formula: =SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100))) juz dont understand how the part works? ~ 1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) thx! "Max" wrote: One way .. Assuming this table is in Sheet1, cols A to C data from row2 down product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 In Sheet2 ------------- With the labels in A1:B1 : UniqueProd, UniqueCust Put in A2: =SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100))) Array-enter the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER Copy A2 across to B2 A2 and B2 will return the number of unique items in col A and B in Sheet1 Adjust the ranges to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Marisa" wrote in message ... I have the data source like below: product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 and my pivot table like below: customer product total A 1 1000 2 1000 A Count 2 B 1 500 3 200 B Count 2 How can I get the number of customer = 2 (A, B)? and number of product = 3 (1, 2, 3)? It seems to me that the count on pivot table only count the number of row of that data. Can I get this count from pivot table or other excel formula? |
#6
|
|||
|
|||
"kalz" wrote
I'm also interested to know how to construct ur prev. formula: =SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100))) juz dont understand how the part works? ~ 1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) ok, here's an attempt to explain (others could do this much better <g) Based on the sample data, A2:A100 in Sheet1 contains: 1 2 1 3 <rest are blanks For the sample data, the part: LEN(Sheet1!A2:A100)0 will resolve to: {TRUE;TRUE;TRUE;TRUE;FALSE;...FALSE} The IF(LEN(Sheet1!A2:A100)0 will "pick" only the parts in the return from: 1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) corresponding to TRUE, i.e. it'll pick the first 4 values in the return from the 1/COUNTIF(...) (**bear this in mind) COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) compares each cell in A2:A100 in Sheet1 to each of the other cells in the same range and returns an array containing counts The return from COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) would be: 2 1 2 1 <rest are zeros as there are two 1s, one 2 and one 3 in A2:A100 in Sheet1 So 1/COUNTIF(...) would return 0.5 (=1/2) 1 (=1/1) 0.5 (=1/2) 1 (=1/1) <rest are #DIV/0! (=1/0) As only the first 4 values in the return from the 1/COUNTIF(...) will be picked (see ** above), hence the expression: SUM(IF(LEN(...)0,1/COUNTIF(...))) would ultimately resolve to : 0.5 + 1 + 0.5 + 1 = 3 which is the number of uniques in the range -- Rgds Max xl 97 -- GMT+8, 1 22' N 103 45' E xdemechanik <atyahoo<dotcom --- |
#7
|
|||
|
|||
its great! thx!
"Max" wrote: "kalz" wrote I'm also interested to know how to construct ur prev. formula: =SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100))) juz dont understand how the part works? ~ 1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) ok, here's an attempt to explain (others could do this much better <g) Based on the sample data, A2:A100 in Sheet1 contains: 1 2 1 3 <rest are blanks For the sample data, the part: LEN(Sheet1!A2:A100)0 will resolve to: {TRUE;TRUE;TRUE;TRUE;FALSE;...FALSE} The IF(LEN(Sheet1!A2:A100)0 will "pick" only the parts in the return from: 1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) corresponding to TRUE, i.e. it'll pick the first 4 values in the return from the 1/COUNTIF(...) (**bear this in mind) COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) compares each cell in A2:A100 in Sheet1 to each of the other cells in the same range and returns an array containing counts The return from COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) would be: 2 1 2 1 <rest are zeros as there are two 1s, one 2 and one 3 in A2:A100 in Sheet1 So 1/COUNTIF(...) would return 0.5 (=1/2) 1 (=1/1) 0.5 (=1/2) 1 (=1/1) <rest are #DIV/0! (=1/0) As only the first 4 values in the return from the 1/COUNTIF(...) will be picked (see ** above), hence the expression: SUM(IF(LEN(...)0,1/COUNTIF(...))) would ultimately resolve to : 0.5 + 1 + 0.5 + 1 = 3 which is the number of uniques in the range -- Rgds Max xl 97 -- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom --- |
#8
|
|||
|
|||
You're welcome !
Thanks for posting back -- Rgds Max xl 97 --- GMT+8, 1 22' N 103 45' E xdemechanik <atyahoo<dotcom ---- "kalz" wrote its great! thx! |
#9
|
|||
|
|||
I have been struggling with this same problem, but I need to find a solution
that works in a Pivot Table calculated field... I am using a Pivot Table to view data from a very large database. The Pivot Table has the following fields: Customer, Quarter and Sales. There are many duplicate Customer sales in the database. I would like to create a Pivot Table that calculates the # of unique customers in each quarter. If I use the Pivot Table Count function it double counts all of the duplicate Customer entries. Can I create a Calculated Field that accomplishes this? I have tried using the formula you provided, =SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100))), but since I am using it in a Pivot Table calculated field I used the following format: =SUM(IF(LEN(Customer)0,1/COUNTIF(Customer, Customer))). Where Customer is inserted from the Pivot Table field list. I get a The function contains an error message. Can you help me understand what I am doing wrong? Thanks, Mike "Marisa" wrote: I have the data source like below: product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 and my pivot table like below: customer product total A 1 1000 2 1000 A Count 2 B 1 500 3 200 B Count 2 How can I get the number of customer = 2 (A, B)? and number of product = 3 (1, 2, 3)? It seems to me that the count on pivot table only count the number of row of that data. Can I get this count from pivot table or other excel formula? |
#10
|
|||
|
|||
Can you help me understand what I am doing wrong?
The suggested formula is an array formula, and from Excel Help (xl97) for calculated field syntax: ".. you cannot use array functions. " But you could try using the formula directly on the source data set using a dynamic range (Cust) for the customers Suppose the source data is in Sheet1, with Customers listed in col B, B2 down (the listing in col B is assumed continuous, without any in-between blank rows) Click Insert Name Define Under Names in workbook, enter: Cust In the "Refers to" box, put: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1,) Click OK (The above will create a dynamic range: Cust) In Sheet2 (say), copy and paste into the formula bar for say, B2: =SUM(IF(LEN(Cust)0,1/COUNTIF(Cust,Cust))) Remember to array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) (if you don't array-enter, you'll get the error #VALUE!) B2 should return the unique counts of the customers in col B of Sheet1 And if the counts are out, the problem probably lies with the source data (not clean) You could try using TRIM() to clean up any extraneous white spaces (not apparent) E.g.: In Sheet1, use an empty col to the right, say col X?, put in X2: =TRIM(B2) copy down, then copy col X and overwrite col B with a paste-special values ok -- Rgds Max xl 97 --- GMT+8, 1 22' N 103 45' E xdemechanik <atyahoo<dotcom ---- "Mike Struckman" wrote I have been struggling with this same problem, but I need to find a solution that works in a Pivot Table calculated field... I am using a Pivot Table to view data from a very large database. The Pivot Table has the following fields: Customer, Quarter and Sales. There are many duplicate Customer sales in the database. I would like to create a Pivot Table that calculates the # of unique customers in each quarter. If I use the Pivot Table Count function it double counts all of the duplicate Customer entries. Can I create a Calculated Field that accomplishes this? I have tried using the formula you provided, "=SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))" , but since I am using it in a Pivot Table calculated field I used the following format: "=SUM(IF(LEN(Customer)0,1/COUNTIF(Customer, Customer)))". Where "Customer" is inserted from the Pivot Table field list. I get a "The function contains an error" message. Can you help me understand what I am doing wrong? Thanks, Mike |
#11
|
|||
|
|||
Thanks Max, but I don't have direct access to the source data. I need to find
a way to do it with the Pivot Table Calculated Field. Is there no way to do this? Seems like getting a unique count would be a common thing to want to do with Pivot Tables. Mike "Max" wrote: Can you help me understand what I am doing wrong? The suggested formula is an array formula, and from Excel Help (xl97) for calculated field syntax: ".. you cannot use array functions. " But you could try using the formula directly on the source data set using a dynamic range (Cust) for the customers Suppose the source data is in Sheet1, with Customers listed in col B, B2 down (the listing in col B is assumed continuous, without any in-between blank rows) Click Insert Name Define Under Names in workbook, enter: Cust In the "Refers to" box, put: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1,) Click OK (The above will create a dynamic range: Cust) In Sheet2 (say), copy and paste into the formula bar for say, B2: =SUM(IF(LEN(Cust)0,1/COUNTIF(Cust,Cust))) Remember to array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) (if you don't array-enter, you'll get the error #VALUE!) B2 should return the unique counts of the customers in col B of Sheet1 And if the counts are out, the problem probably lies with the source data (not clean) You could try using TRIM() to clean up any extraneous white spaces (not apparent) E.g.: In Sheet1, use an empty col to the right, say col X?, put in X2: =TRIM(B2) copy down, then copy col X and overwrite col B with a paste-special values ok -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Mike Struckman" wrote I have been struggling with this same problem, but I need to find a solution that works in a Pivot Table calculated field... I am using a Pivot Table to view data from a very large database. The Pivot Table has the following fields: Customer, Quarter and Sales. There are many duplicate Customer sales in the database. I would like to create a Pivot Table that calculates the # of unique customers in each quarter. If I use the Pivot Table Count function it double counts all of the duplicate Customer entries. Can I create a Calculated Field that accomplishes this? I have tried using the formula you provided, "=SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))" , but since I am using it in a Pivot Table calculated field I used the following format: "=SUM(IF(LEN(Customer)0,1/COUNTIF(Customer, Customer)))". Where "Customer" is inserted from the Pivot Table field list. I get a "The function contains an error" message. Can you help me understand what I am doing wrong? Thanks, Mike |
#12
|
|||
|
|||
Sorry, Mike. I've no further comments to offer you.
Hang around awhile for possible insights from others. -- Rgds Max xl 97 --- GMT+8, 1 22' N 103 45' E xdemechanik <atyahoo<dotcom ---- "Mike Struckman" wrote in message ... Thanks Max, but I don't have direct access to the source data. I need to find a way to do it with the Pivot Table Calculated Field. Is there no way to do this? Seems like getting a unique count would be a common thing to want to do with Pivot Tables. Mike |
#13
|
|||
|
|||
Hi Mike,
Today I find a work around if you only need the unique count of the first column of your pivot. Actually it is a basic function in excel. There is a auto-calculation result on the left bottom on excel screen, which provide auto-calculation of SUM, AVG, COUNT.... First, you change the auto-calculation to COUNT. Select the range on the first column, it gives the unique count on the auto-calculation result. Hope this help! Marisa "Mike Struckman" wrote: Thanks Max, but I don't have direct access to the source data. I need to find a way to do it with the Pivot Table Calculated Field. Is there no way to do this? Seems like getting a unique count would be a common thing to want to do with Pivot Tables. Mike "Max" wrote: Can you help me understand what I am doing wrong? The suggested formula is an array formula, and from Excel Help (xl97) for calculated field syntax: ".. you cannot use array functions. " But you could try using the formula directly on the source data set using a dynamic range (Cust) for the customers Suppose the source data is in Sheet1, with Customers listed in col B, B2 down (the listing in col B is assumed continuous, without any in-between blank rows) Click Insert Name Define Under Names in workbook, enter: Cust In the "Refers to" box, put: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1,) Click OK (The above will create a dynamic range: Cust) In Sheet2 (say), copy and paste into the formula bar for say, B2: =SUM(IF(LEN(Cust)0,1/COUNTIF(Cust,Cust))) Remember to array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) (if you don't array-enter, you'll get the error #VALUE!) B2 should return the unique counts of the customers in col B of Sheet1 And if the counts are out, the problem probably lies with the source data (not clean) You could try using TRIM() to clean up any extraneous white spaces (not apparent) E.g.: In Sheet1, use an empty col to the right, say col X?, put in X2: =TRIM(B2) copy down, then copy col X and overwrite col B with a paste-special values ok -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Mike Struckman" wrote I have been struggling with this same problem, but I need to find a solution that works in a Pivot Table calculated field... I am using a Pivot Table to view data from a very large database. The Pivot Table has the following fields: Customer, Quarter and Sales. There are many duplicate Customer sales in the database. I would like to create a Pivot Table that calculates the # of unique customers in each quarter. If I use the Pivot Table Count function it double counts all of the duplicate Customer entries. Can I create a Calculated Field that accomplishes this? I have tried using the formula you provided, "=SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))" , but since I am using it in a Pivot Table calculated field I used the following format: "=SUM(IF(LEN(Customer)0,1/COUNTIF(Customer, Customer)))". Where "Customer" is inserted from the Pivot Table field list. I get a "The function contains an error" message. Can you help me understand what I am doing wrong? Thanks, Mike |
#14
|
|||
|
|||
I have a similar question, except, I need to count unique instances where
there is another variable. Sample Data: Program Student University 1 John North University 1 Steven North University 1 James South University 2 Susie Case University 2 Laura West University 2 Lisa North University So, in this sample data, I would need to count the number of unique universities per program, that is, Program 1 - 2 univeristies, program 2 - 3 universities. Additionally, I need to ensure that the formula updates upon opening, since the data set is in another sheet, and will be updated frequently. "Marisa" wrote: I have the data source like below: product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 and my pivot table like below: customer product total A 1 1000 2 1000 A Count 2 B 1 500 3 200 B Count 2 How can I get the number of customer = 2 (A, B)? and number of product = 3 (1, 2, 3)? It seems to me that the count on pivot table only count the number of row of that data. Can I get this count from pivot table or other excel formula? |
#15
|
|||
|
|||
Marisa -
FWIW, I've gotten fairly good at this Access Pivot Table stuff, and I haven't found a way to do it. If no one else has either, maybe MS should add this capability to the next Access version! Kevin "Marisa" wrote: I have the data source like below: product customer quantity 1 A 1000 2 A 1000 1 B 500 3 B 200 and my pivot table like below: customer product total A 1 1000 2 1000 A Count 2 B 1 500 3 200 B Count 2 How can I get the number of customer = 2 (A, B)? and number of product = 3 (1, 2, 3)? It seems to me that the count on pivot table only count the number of row of that data. Can I get this count from pivot table or other excel formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table services | Excel Discussion (Misc queries) | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) | |||
pivot table multi line chart | Charts and Charting in Excel | |||
Problem with Pivot Table Drop-Down Menus | Excel Worksheet Functions | |||
Pivot table, how do you exclude counting cells with formulas as a | Excel Worksheet Functions |