| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count values from Pivot Table
Sorry - I'm asking for help again!
Using Excel 2002 I have a sheet with many thousands of rows of data and I'm trying to use pivot tables to analyse my data as follows: Heres a sample of my data Date Product Qty Customer 01/09/2007 Apple 1 2317 01/09/2007 Banana 1 2317 01/09/2007 Carrot 1 2317 01/09/2007 Apple 1 2324 01/09/2007 Apple 1 2326 01/10/2007 Apple 1 2352 01/10/2007 Banana 1 2352 01/10/2007 Apple 1 2354 01/10/2007 Apple 2 2365 01/10/2007 Carrot 1 2367 01/10/2007 Apple 1 2370 01/10/2007 Apple 1 2382 01/10/2007 Banana 1 2382 01/10/2007 Apple 1 2382 01/10/2007 Apple 1 2383 01/10/2007 Carrot 1 2383 01/10/2007 Apple 1 2385 01/10/2007 Apple 1 2386 01/10/2007 Carrot 2 2386 Heres my pivot table Sum of Qty Date Customer Total 01/09/07 2317 3 2324 1 2326 1 01/09/07 Total 5 01/10/07 2352 2 2354 1 2365 2 2367 1 2370 1 2382 3 2383 2 2385 1 2386 3 01/10/07 Total 16 Grand Total 21 What Im trying to achieve is a daily count of number of items purchased. I don't care about who bought what item. i.e. 1 item 2 items 3 items 1/9/2007 2 1 1/10/2007 4 3 2 Can this be done using pivot tables, or am I barking up the wrong tree? I had thought I might need to create another pivot table based on my original one, however I cant analyse by the results in the €˜total column. As always, all help very gratefully received. My Excel knowledge is vastly improved thanks to this forum, but Ive obviously still got a way to go! Sorry it's such a lengthy question. Many thanks Les |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
One way .. add a helper col E in the source table
Label in E1: #Items In E2: =IF(A2="","",IF(SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2) )<COUNTIF(D:D,D2),"",COUNTIF(D:D,D2))) Copy E2 down. Pivot on the extended source table, place Date in ROW, #Items in COLUMN, #Items in DATA (as Count) to return the required results -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "lesg46" wrote: Sorry - I'm asking for help again! Using Excel 2002 I have a sheet with many thousands of rows of data and I'm trying to use pivot tables to analyse my data as follows: Heres a sample of my data Date Product Qty Customer 01/09/2007 Apple 1 2317 01/09/2007 Banana 1 2317 01/09/2007 Carrot 1 2317 01/09/2007 Apple 1 2324 01/09/2007 Apple 1 2326 01/10/2007 Apple 1 2352 01/10/2007 Banana 1 2352 01/10/2007 Apple 1 2354 01/10/2007 Apple 2 2365 01/10/2007 Carrot 1 2367 01/10/2007 Apple 1 2370 01/10/2007 Apple 1 2382 01/10/2007 Banana 1 2382 01/10/2007 Apple 1 2382 01/10/2007 Apple 1 2383 01/10/2007 Carrot 1 2383 01/10/2007 Apple 1 2385 01/10/2007 Apple 1 2386 01/10/2007 Carrot 2 2386 Heres my pivot table Sum of Qty Date Customer Total 01/09/07 2317 3 2324 1 2326 1 01/09/07 Total 5 01/10/07 2352 2 2354 1 2365 2 2367 1 2370 1 2382 3 2383 2 2385 1 2386 3 01/10/07 Total 16 Grand Total 21 What Im trying to achieve is a daily count of number of items purchased. I don't care about who bought what item. i.e. 1 item 2 items 3 items 1/9/2007 2 1 1/10/2007 4 3 2 Can this be done using pivot tables, or am I barking up the wrong tree? I had thought I might need to create another pivot table based on my original one, however I cant analyse by the results in the €˜total column. As always, all help very gratefully received. My Excel knowledge is vastly improved thanks to this forum, but Ive obviously still got a way to go! Sorry it's such a lengthy question. Many thanks Les |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
Hi,
You may also use Calculated formulas and calculated items in pivot tables. Please free to mail your question to me at -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "lesg46" wrote in message ... Sorry - I'm asking for help again! Using Excel 2002 I have a sheet with many thousands of rows of data and I'm trying to use pivot tables to analyse my data as follows: Heres a sample of my data Date Product Qty Customer 01/09/2007 Apple 1 2317 01/09/2007 Banana 1 2317 01/09/2007 Carrot 1 2317 01/09/2007 Apple 1 2324 01/09/2007 Apple 1 2326 01/10/2007 Apple 1 2352 01/10/2007 Banana 1 2352 01/10/2007 Apple 1 2354 01/10/2007 Apple 2 2365 01/10/2007 Carrot 1 2367 01/10/2007 Apple 1 2370 01/10/2007 Apple 1 2382 01/10/2007 Banana 1 2382 01/10/2007 Apple 1 2382 01/10/2007 Apple 1 2383 01/10/2007 Carrot 1 2383 01/10/2007 Apple 1 2385 01/10/2007 Apple 1 2386 01/10/2007 Carrot 2 2386 Heres my pivot table Sum of Qty Date Customer Total 01/09/07 2317 3 2324 1 2326 1 01/09/07 Total 5 01/10/07 2352 2 2354 1 2365 2 2367 1 2370 1 2382 3 2383 2 2385 1 2386 3 01/10/07 Total 16 Grand Total 21 What Im trying to achieve is a daily count of number of items purchased. I don't care about who bought what item. i.e. 1 item 2 items 3 items 1/9/2007 2 1 1/10/2007 4 3 2 Can this be done using pivot tables, or am I barking up the wrong tree? I had thought I might need to create another pivot table based on my original one, however I cant analyse by the results in the €˜total column. As always, all help very gratefully received. My Excel knowledge is vastly improved thanks to this forum, but Ive obviously still got a way to go! Sorry it's such a lengthy question. Many thanks Les |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
Sorry, correction to the earlier response ..
One way .. add 2 helper cols E & F in the source table Labels in E1:F1, eg: Int, #Items In E2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2))) In F2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula): =IF(E2<MAX(IF((A$2:A$100=A2)*(D$2:D$100=D2),E$2:E$ 100)),"",E2) (adapt the ranges to suit your actual extents) Copy E2:F2 down. Pivot on the extended source table, place Date in ROW, #Items in COLUMN, #Items in DATA (as Count) to return the required results -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
You may also use Calculated formulas and calculated items in pivot tables.
I'm not sure if that's possible in this instance. Given the OP's sample data as posted, how would the steps to do it using calculated formulas/items look like? (I don't know) Please free to mail your question to me at ... But wouldn't it be much better to benefit all (ie the OP & all other interested readers) if queries & responses/answers are kept online/visible here in the newsgroups? That's the way I understand it & practise. -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800, Files:359, Subscribers:56 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
Hi,
How are you Max. I am absolutely in sync with what you say. The only reason I mention that statement is many a times it is difficult to understand the problem without a spreadsheet. I will nevertheless try to answer the question in the newsgroups itself. Thank you for the feedback. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Max" wrote in message ... You may also use Calculated formulas and calculated items in pivot tables. I'm not sure if that's possible in this instance. Given the OP's sample data as posted, how would the steps to do it using calculated formulas/items look like? (I don't know) Please free to mail your question to me at ... But wouldn't it be much better to benefit all (ie the OP & all other interested readers) if queries & responses/answers are kept online/visible here in the newsgroups? That's the way I understand it & practise. -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800, Files:359, Subscribers:56 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
Hi Max,
This almost works! Heres the result I get with your formulas. date Product qty Cust. Int Items 12/01/2007 Apple 1 6057 1 1 12/01/2007 Apple 2 6058 1 12/01/2007 Banana 1 6058 2 2 12/01/2007 Apple 1 6059 1 12/01/2007 Banana 1 6059 2 12/01/2007 Carrot 2 6059 3 3 However it doesn't take into account the quantity of each item that the customer may have bought, just the number of times the customer number appears on a particular day. Somehow Column C (Qty) needs to come into play too. So that for customer 6058 I get the result of 3, and for 6059 I get 4. Im now way out of my depth, so if you can help further Id be most grateful. Thanks again, Lesley "Max" wrote: Sorry, correction to the earlier response .. One way .. add 2 helper cols E & F in the source table Labels in E1:F1, eg: Int, #Items In E2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2))) In F2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula): =IF(E2<MAX(IF((A$2:A$100=A2)*(D$2:D$100=D2),E$2:E$ 100)),"",E2) (adapt the ranges to suit your actual extents) Copy E2:F2 down. Pivot on the extended source table, place Date in ROW, #Items in COLUMN, #Items in DATA (as Count) to return the required results -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
In E2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2)))
Try changing the formula in E2 to: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2),C$ 2:C2)) Copy down. Refresh the pivot. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "lesg46" wrote: Hi Max, This almost works! Heres the result I get with your formulas. date Product qty Cust. Int Items 12/01/2007 Apple 1 6057 1 1 12/01/2007 Apple 2 6058 1 12/01/2007 Banana 1 6058 2 2 12/01/2007 Apple 1 6059 1 12/01/2007 Banana 1 6059 2 12/01/2007 Carrot 2 6059 3 3 However it doesn't take into account the quantity of each item that the customer may have bought, just the number of times the customer number appears on a particular day. Somehow Column C (Qty) needs to come into play too. So that for customer 6058 I get the result of 3, and for 6059 I get 4. Im now way out of my depth, so if you can help further Id be most grateful. Thanks again, Lesley |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
Hi Max,
Have tried your revised formula but I'm afraid it doesn't work. I get this result date Product qty cust. Int Items 12/01/2007 Apple 1 6057 1 12/01/2007 Apple 2 6058 1 12/01/2007 Banana 1 6058 1 12/01/2007 Apple 1 6059 1 12/01/2007 Banana 1 6059 1 12/01/2007 Carrot 2 6059 1 All Column E changes to 1 and no item counts. Lesley "Max" wrote: In E2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2))) Try changing the formula in E2 to: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2),C$ 2:C2)) Copy down. Refresh the pivot. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "lesg46" wrote: Hi Max, This almost works! Heres the result I get with your formulas. date Product qty Cust. Int Items 12/01/2007 Apple 1 6057 1 1 12/01/2007 Apple 2 6058 1 12/01/2007 Banana 1 6058 2 2 12/01/2007 Apple 1 6059 1 12/01/2007 Banana 1 6059 2 12/01/2007 Carrot 2 6059 3 3 However it doesn't take into account the quantity of each item that the customer may have bought, just the number of times the customer number appears on a particular day. Somehow Column C (Qty) needs to come into play too. So that for customer 6058 I get the result of 3, and for 6059 I get 4. Im now way out of my depth, so if you can help further Id be most grateful. Thanks again, Lesley |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
Pl re-try/check again over there
I got the results below using the revised E2, copied down (no change to col F's formulas) date Product qty cust Int Items 12/1/2007 Apple 1 6057 1 1 12/1/2007 Apple 2 6058 2 12/1/2007 Banana 1 6058 3 3 12/1/2007 Apple 1 6059 1 12/1/2007 Banana 1 6059 2 12/1/2007 Carrot 2 6059 4 4 The cols E & F construct for the above, once again E2, copied down: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2),C$ 2:C2)) F2, array-entered*, then copied down: =IF(E2<MAX(IF((A$2:A$100=A2)*(D$2:D$100=D2),E$2:E$ 100)),"",E2) *press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "lesg46" wrote: Hi Max, Have tried your revised formula but I'm afraid it doesn't work. I get this result date Product qty cust. Int Items 12/01/2007 Apple 1 6057 1 12/01/2007 Apple 2 6058 1 12/01/2007 Banana 1 6058 1 12/01/2007 Apple 1 6059 1 12/01/2007 Banana 1 6059 1 12/01/2007 Carrot 2 6059 1 All Column E changes to 1 and no item counts. Lesley |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
Hi Max,
After much confusion I have got it to work. I had checked and double checked the formulas yesterday in the office, but it was doing some very strange things... Returning different values each time or sometimes returning no values at all (all using the same formula). I recreated the spreadsheet on my home PC last night, which is running Excel 2003 and it worked first time! Back in the office this morning with 2002, similar problems to yesterday, so I started all over again from the beginning with a clean sheet with all my data in, and I finally have the results I need. Seems on my PC if I don't get it right first time, it doesn't work at all. Perhaps I need to upgrade both my office PC and Excel! Many many thanks for all your help and patience with this. You've saved me a huge amount of time and severe boredom from doing it almost manually! Best regards Lesley "Max" wrote: Pl re-try/check again over there I got the results below using the revised E2, copied down (no change to col F's formulas) date Product qty cust Int Items 12/1/2007 Apple 1 6057 1 1 12/1/2007 Apple 2 6058 2 12/1/2007 Banana 1 6058 3 3 12/1/2007 Apple 1 6059 1 12/1/2007 Banana 1 6059 2 12/1/2007 Carrot 2 6059 4 4 The cols E & F construct for the above, once again E2, copied down: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2),C$ 2:C2)) F2, array-entered*, then copied down: =IF(E2<MAX(IF((A$2:A$100=A2)*(D$2:D$100=D2),E$2:E$ 100)),"",E2) *press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "lesg46" wrote: Hi Max, Have tried your revised formula but I'm afraid it doesn't work. I get this result date Product qty cust. Int Items 12/01/2007 Apple 1 6057 1 12/01/2007 Apple 2 6058 1 12/01/2007 Banana 1 6058 1 12/01/2007 Apple 1 6059 1 12/01/2007 Banana 1 6059 1 12/01/2007 Carrot 2 6059 1 All Column E changes to 1 and no item counts. Lesley |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count values from Pivot Table
Welcome, glad you got it working !
-- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "lesg46" wrote in message ... Hi Max, After much confusion I have got it to work. I had checked and double checked the formulas yesterday in the office, but it was doing some very strange things... Returning different values each time or sometimes returning no values at all (all using the same formula). I recreated the spreadsheet on my home PC last night, which is running Excel 2003 and it worked first time! Back in the office this morning with 2002, similar problems to yesterday, so I started all over again from the beginning with a clean sheet with all my data in, and I finally have the results I need. Seems on my PC if I don't get it right first time, it doesn't work at all. Perhaps I need to upgrade both my office PC and Excel! Many many thanks for all your help and patience with this. You've saved me a huge amount of time and severe boredom from doing it almost manually! Best regards Lesley |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
| How to DISPLAY all data values in Pivot Table...not count, sum, etc | Excel Discussion (Misc queries) | |||
| Count unique values - Pivot Table | Excel Discussion (Misc queries) | |||
| Count unique values - Pivot Table | Charts and Charting in Excel | |||
| Count Distinct Values by Group Using Pivot Table (NM) | Excel Worksheet Functions | |||
Linear Mode
